﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Intelligent.OTC.Common.UnitOfWork;
using Intelligent.OTC.Domain.DataModel;
using Intelligent.OTC.Domain.Repositories;
using System.Configuration;
using System.IO;
using Intelligent.OTC.Domain;
using Intelligent.OTC.Common;
using System.Data.Entity.Validation;
//using Intelligent.OTC.Common;
using Intelligent.OTC.Common.Attr;
using Intelligent.OTC.Common.Utils;
using Intelligent.OTC.Common.Exceptions;
using System.Data.Entity;
using System.Web;
using System.Text.RegularExpressions;
using Intelligent.OTC.Common.Repository;
using System.Transactions;
using System.Data.SqlClient;
using System.Data;
using EntityFramework.BulkInsert.Extensions;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Core.Metadata.Edm;
using System.Collections.ObjectModel;
using EntityFramework.MappingAPI.Mappings;
using System.Reflection;
using EntityFramework.MappingAPI.Exceptions;

namespace Intelligent.OTC.Business
{
    public class CustomerService : ICustomerService
    {
        #region constant definition
        public const string UnTouched = "001";
        public const string agingData = "002";
        public const char strSplit = '^';// 分割字符串
        public const char strSubSplit = ' ';// 分割字符串
        public const char strInvSplit = '-';// 分割字符串
        public const string strAccountKey = "AccountLevelPath";//Account路径的config保存名
        public const string strInvoiceKey = "InvoiceLevelPath";//Invoice路径的config保存名
        public const string strAccountStats = "Draft";//初始stats
        public string strInvStats = Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.Open);//初始stats
        public const string strFormatCheck = "Summary Type:^Customer Summary";
        public const string strHeaderCheck = "Customer";
        public const string strInvoiceHeaderCheck = "Statement Date";
        public const string strSubTatolCheck = "Sub-total";
        public const string strEnd = "All Customers";
        public const int intAccountCols = 31;
        public const int intInvoiceCols = 29;
        public string strMessage = "";
        //public string strEnter = "</br>";
        public string strEnter = "\r";
        public string strDtCheck = "Report Date:";
        public string strTempPathKey = "TemplateDownloadPath";
//        public string strTempPathInvKey = "TemplateInvDownloadPath";
        public string strArchivePathKey = "ArchiveDownloadPath";
        public string CurrentDeal 
        {
            get { 
                return AppContext.Current.User.Deal.ToString();
            }
        }
        public DateTime CurrentTime
        {
            get
            {
                return AppContext.Current.User.Now;
            }
        }
        #endregion

        #region variable definition
        //public StreamReader sr { get; set; }
        //public StreamReader srinv { get; set; }

        public OTCRepository CommonRep { get; set; }
        public ICacheService CacheSvr { get; set; }
        public List<Customer> custList;
        public IBaseDataService BDService { get; set; }

        //public string strSiteAcc;
        //public string strSiteInv;

        public List<CustomerAgingStaging> listAgingStaging;

        public List<InvoiceAgingStaging> invoiceAgingList;

        public List<CustomerAgingStaging> listAgingStagingEx;//导入前数据

        public List<InvoiceAgingStaging> invoiceAgingListEx;//导入前数据

        public string[] CustomerInfo;

        #endregion

        /// <summary>
        /// get all CustomerAgingStaging Data from Db
        /// </summary>
        /// <returns></returns>
        public IQueryable<CustomerAgingStaging> GetCustomerAgingStaging()
        {
            return CommonRep.GetQueryable<CustomerAgingStaging>().Where(o => o.Deal == CurrentDeal);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IQueryable<InvoiceAgingStaging> GetInvoiceAgingStaging()
        {
            return CommonRep.GetQueryable<InvoiceAgingStaging>().Where(o => o.Deal == CurrentDeal);
        }

        /// <summary>
        /// get all CustomerAging Data from Db
        /// </summary>
        /// <returns></returns>
        public IQueryable<CustomerAging> GetCustomerAging()
        {
            return CommonRep.GetQueryable<CustomerAging>().Where(o => o.Deal == CurrentDeal);
        }


        /// <summary>
        /// Get customer basics and level information
        /// </summary>
        /// <param name="latestPId"></param>
        /// <returns></returns>
        public IQueryable<CustomerLevelDto> GetCustomerLevel(int latestPId)
        {
            var customerLevel = from cust in CommonRep.GetDbSet<Customer>()
                                join grp in CommonRep.GetDbSet<CustomerGroupCfg>()
                                on new { cust.BillGroupCode, cust.Deal } equals new { grp.BillGroupCode, grp.Deal }
                                into grps
                                from grp in grps.DefaultIfEmpty()
                                where cust.RemoveFlg == "1" && cust.ExcludeFlg == "0" && cust.Deal == AppContext.Current.User.Deal
                                && ((grp.Collector == AppContext.Current.User.EID) || (string.IsNullOrEmpty(grp.Collector) && cust.Collector == AppContext.Current.User.EID))
                                join changeHis in CommonRep.GetDbSet<CustomerChangeHis>().Where(g => g.PeriodId == latestPId && g.VrType == "1").OrderByDescending(t=>t.Id)
                                on new { cust.CustomerNum, cust.Deal } equals new { changeHis.CustomerNum, changeHis.Deal }
                                    into valueHis
                                join changeHis in CommonRep.GetDbSet<CustomerChangeHis>().Where(g => g.PeriodId == latestPId && g.VrType == "2").OrderByDescending(t=>t.Id)
                                on new { cust.CustomerNum, cust.Deal } equals new { changeHis.CustomerNum, changeHis.Deal }
                                   into riskHis
                                select new CustomerLevelDto
                                {
                                 Id = cust.Id,
                                 Deal = cust.Deal,
                                 CustomerNum = cust.CustomerNum,
                                 CustomerName = cust.CustomerName,
                                 // if group not exist, use customer name as group code and name.
                                 BillGroupCode = string.IsNullOrEmpty(grp.BillGroupCode) ? cust.CustomerName : grp.BillGroupCode,
                                 BillGroupName = string.IsNullOrEmpty(grp.BillGroupName) ? cust.CustomerName : grp.BillGroupName,
                                 Collector = string.IsNullOrEmpty(grp.Collector) ? cust.Collector : grp.Collector,
                                 Value = valueHis.FirstOrDefault().Value,
                                 Risk = riskHis.FirstOrDefault().Value,
                                 ValueLevel = valueHis.FirstOrDefault().ValueLevel,
                                 RiskLevel = riskHis.FirstOrDefault().ValueLevel
                                };
            return customerLevel;
        }

        /// <summary>
        /// Get customer basics and level information Specially for All customers
        /// </summary>
        /// <param name="latestPId"></param>
        /// <param name="supervisor">1:isSupervisor;2:Not Supervisor</param>
        /// <returns></returns>
        public IQueryable<CustomerLevelDto> GetCustomerLevelForAllCus(int latestPId,int supervisor)
        {
            IQueryable<CustomerLevelDto> customerLevel = null;

            if (supervisor == 1)
            {
                customerLevel = from cust in CommonRep.GetDbSet<Customer>()
                                join grp in CommonRep.GetDbSet<CustomerGroupCfg>()
                                on new { cust.BillGroupCode, cust.Deal } equals new { grp.BillGroupCode, grp.Deal }
                                into grps
                                from grp in grps.DefaultIfEmpty()
                                where cust.RemoveFlg == "1" && cust.ExcludeFlg == "0" && cust.Deal == AppContext.Current.User.Deal
                                join changeHis in CommonRep.GetDbSet<CustomerChangeHis>().Where(g => g.PeriodId == latestPId && g.VrType == "1").OrderByDescending(t => t.Id)
                                on new { cust.CustomerNum, cust.Deal } equals new { changeHis.CustomerNum, changeHis.Deal }
                                    into valueHis
                                join changeHis in CommonRep.GetDbSet<CustomerChangeHis>().Where(g => g.PeriodId == latestPId && g.VrType == "2").OrderByDescending(t => t.Id)
                                on new { cust.CustomerNum, cust.Deal } equals new { changeHis.CustomerNum, changeHis.Deal }
                                    into riskHis
                                select new CustomerLevelDto
                                {
                                    Id = cust.Id,
                                    Deal = cust.Deal,
                                    CustomerNum = cust.CustomerNum,
                                    CustomerName = cust.CustomerName,
                                    // if group not exist, use customer name as group code and name.
                                    BillGroupCode = string.IsNullOrEmpty(grp.BillGroupCode) ? cust.CustomerName : grp.BillGroupCode,
                                    BillGroupName = string.IsNullOrEmpty(grp.BillGroupName) ? cust.CustomerName : grp.BillGroupName,
                                    Collector = string.IsNullOrEmpty(grp.Collector) ? cust.Collector : grp.Collector,
                                    Value = valueHis.FirstOrDefault().Value,
                                    Risk = riskHis.FirstOrDefault().Value,
                                    ValueLevel = valueHis.FirstOrDefault().ValueLevel,
                                    RiskLevel = riskHis.FirstOrDefault().ValueLevel
                                };
            }
            else
            {
                customerLevel = from cust in CommonRep.GetDbSet<Customer>()
                            join grp in CommonRep.GetDbSet<CustomerGroupCfg>()
                            on new { cust.BillGroupCode, cust.Deal } equals new { grp.BillGroupCode, grp.Deal }
                            into grps
                            from grp in grps.DefaultIfEmpty()
                            where cust.RemoveFlg == "1" && cust.ExcludeFlg == "0" && cust.Deal == AppContext.Current.User.Deal
                            && ((grp.Collector == AppContext.Current.User.EID) || (string.IsNullOrEmpty(grp.Collector) && cust.Collector == AppContext.Current.User.EID))
                            join changeHis in CommonRep.GetDbSet<CustomerChangeHis>().Where(g => g.PeriodId == latestPId && g.VrType == "1").OrderByDescending(t => t.Id)
                            on new { cust.CustomerNum, cust.Deal } equals new { changeHis.CustomerNum, changeHis.Deal }
                                into valueHis
                            join changeHis in CommonRep.GetDbSet<CustomerChangeHis>().Where(g => g.PeriodId == latestPId && g.VrType == "2").OrderByDescending(t => t.Id)
                            on new { cust.CustomerNum, cust.Deal } equals new { changeHis.CustomerNum, changeHis.Deal }
                                into riskHis
                            select new CustomerLevelDto
                            {
                                Id = cust.Id,
                                Deal = cust.Deal,
                                CustomerNum = cust.CustomerNum,
                                CustomerName = cust.CustomerName,
                                // if group not exist, use customer name as group code and name.
                                BillGroupCode = string.IsNullOrEmpty(grp.BillGroupCode) ? cust.CustomerName : grp.BillGroupCode,
                                BillGroupName = string.IsNullOrEmpty(grp.BillGroupName) ? cust.CustomerName : grp.BillGroupName,
                                Collector = string.IsNullOrEmpty(grp.Collector) ? cust.Collector : grp.Collector,
                                Value = valueHis.FirstOrDefault().Value,
                                Risk = riskHis.FirstOrDefault().Value,
                                ValueLevel = valueHis.FirstOrDefault().ValueLevel,
                                RiskLevel = riskHis.FirstOrDefault().ValueLevel
                            };
            }
            return customerLevel;
        }

        public IQueryable<CustomerMasterDto> GetCustomerMasterForCurrentUser()
        {
            return from cust in CommonRep.GetDbSet<Customer>()
                   join grp in CommonRep.GetDbSet<CustomerGroupCfg>()
                   on new { cust.BillGroupCode, cust.Deal } equals new { grp.BillGroupCode, grp.Deal }
                   into grps
                   from grp in grps.DefaultIfEmpty()
                   where cust.RemoveFlg == "1" && cust.ExcludeFlg == "0" && cust.Deal == AppContext.Current.User.Deal
                   && ((grp.Collector == AppContext.Current.User.EID) || (string.IsNullOrEmpty(grp.Collector) && cust.Collector == AppContext.Current.User.EID))
                   select new CustomerMasterDto {
                       Id = cust.Id,
                       Deal = cust.Deal,
                       CustomerNum = cust.CustomerNum,
                       CustomerName = cust.CustomerName,
                       // if group not exist, use customer name as group code and name.
                       BillGroupCode = string.IsNullOrEmpty(grp.BillGroupCode) ? cust.CustomerName : grp.BillGroupCode,
                       BillGroupName = string.IsNullOrEmpty(grp.BillGroupName) ? cust.CustomerName : grp.BillGroupName,
                       Collector = string.IsNullOrEmpty(grp.Collector) ? cust.Collector : grp.Collector
                   };
        }

        public IQueryable<CustomerMasterDto> GetCustomerMasterForAllUser()
        {
            return from cust in CommonRep.GetDbSet<Customer>()
                   join grp in CommonRep.GetDbSet<CustomerGroupCfg>()
                   on new { cust.BillGroupCode, cust.Deal } equals new { grp.BillGroupCode, grp.Deal }
                   into grps
                   from grp in grps.DefaultIfEmpty()
                   where cust.RemoveFlg == "1" && cust.ExcludeFlg == "0" && cust.Deal == AppContext.Current.User.Deal
                   select new CustomerMasterDto
                   {
                       Id = cust.Id,
                       Deal = cust.Deal,
                       CustomerNum = cust.CustomerNum,
                       CustomerName = cust.CustomerName,
                       // if group not exist, use customer name as group code and name.
                       BillGroupCode = string.IsNullOrEmpty(grp.BillGroupCode) ? cust.CustomerName : grp.BillGroupCode,
                       BillGroupName = string.IsNullOrEmpty(grp.BillGroupName) ? cust.CustomerName : grp.BillGroupName,
                       Collector = string.IsNullOrEmpty(grp.Collector) ? cust.Collector : grp.Collector
                   };
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IQueryable<CustomerTeam> GetCustomerTeam()
        {
            return CommonRep.GetQueryable<CustomerTeam>().Where(o => o.Deal == CurrentDeal);
        }
    
        public CollectorTeam GetTeamByEid(string eid) 
        { 
            return CommonRep.GetQueryable<CollectorTeam>().Include<CollectorTeam, Team>(o => o.Team).Where(c=>c.Deal==CurrentDeal && c.Collector==eid).FirstOrDefault();
        }

        #region get CustomerAgingStagingHistory Data from Db by ImportId
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<CustomerAgingStaging> GetCustomerAging(string strImportId)
        {
            return CommonRep.GetQueryable<CustomerAgingStaging>().Where(o => o.ImportId == strImportId).Select(o => o).ToList();
        }
        #endregion

        public List<CustomerGroupCfgStaging> GetGroupStaing()
        {
            return CommonRep.GetQueryable<CustomerGroupCfgStaging>().Where(o => o.Deal == CurrentDeal).ToList();
        }

        public List<CustomerAging> GetCurrentPerCustAging()
        {
            //List<CustomerAging> rtnList = new List<CustomerAging>();
            List<string> strImportId = new List<string>();
            SiteService siteService = SpringFactory.GetObjectImpl<SiteService>("SiteService");
            List<Sites> sites = siteService.GetAllSites().Where(o => o.Deal == CurrentDeal).ToList();

            PeroidService perService = SpringFactory.GetObjectImpl<PeroidService>("PeroidService");
            PeriodControl currentPer = perService.getcurrentPeroid();

            FileService fileService = SpringFactory.GetObjectImpl<FileService>("FileService");
            List<FileUploadHistory> file = fileService.GetFileUploadHistory().ToList();
            string strImpId = "";
            foreach (Sites site in sites)
            {
                strImpId = file.Where(o => o.LegalEntity == site.LegalEntity
                                && o.SubmitTime >= currentPer.PeriodBegin
                                && o.SubmitTime <= currentPer.PeriodEnd).OrderByDescending(o => o.SubmitTime).Select(o => o.ImportId).FirstOrDefault();
                if (strImpId != null)
                {
                    strImportId.Add(strImpId);
                }
            }

            return CommonRep.GetQueryable<CustomerAging>().Where(o => strImportId.Contains(o.ImportId)).Select(o => o).ToList();
        }

        #region get all SysConfig Data from Db
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IQueryable<SysConfig> GetSysConfig()
        {
            return CommonRep.GetQueryable<SysConfig>();
        }
        #endregion

        // #region get filefullname from Db By ProcessFlag and FileType and Operator
        // /// <summary>
        // /// upload filefullname get
        // /// </summary>
        // /// <param name="strType"></param>
        // /// <returns></returns>
        // public FileUploadHistory GetNewestData(string strType)
        // {
        //     string strUntreated = Helper.EnumToCode<UploadStates>(UploadStates.Untreated);
        //     return CommonRep.GetQueryable<FileUploadHistory>().Where(o => o.FileType == strType
        //                                 && o.Operator == AppContext.Current.User.EID
        //                                 && o.ProcessFlag == strUntreated).Select(o => o).OrderByDescending(o => o.UploadTime).FirstOrDefault();
        //}
        // #endregion

        #region Get CustomerDetail data
        /// <summary>
        /// GetCustomerDetail 数据做成
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public CustomerDetail GetCustomerDetail(int id)
        {
            DateTime agingDT = new DateTime();
            SysConfig rtnData = GetSysConfig().Where(o => o.CfgCode == agingData).Select(o => o).FirstOrDefault();
            agingDT = dataConvertToDT(rtnData.CfgValue.ToString());
            DateTime agingDT90 = new DateTime();
            agingDT90 = agingDT.AddDays(-90);
            CustomerAging cust = CommonRep.FindBy<CustomerAging>(id);
            string cusClassCode = cust.CustomerClass;
            CustomerDetail custDe = new CustomerDetail();
            ObjectHelper.CopyObject(cust, custDe);
            custDe.DueOver180Amt = cust.Due210Amt + cust.Due240Amt + cust.Due270Amt
                                    + cust.Due300Amt + cust.Due360Amt;
            custDe.DueOver90Amt = custDe.DueOver180Amt + cust.Due120Amt + cust.Due150Amt;
            custDe.DueOver60Amt = custDe.DueOver90Amt;
            custDe.DueOver30Amt = custDe.DueOver60Amt;
            custDe.DueOver0Amt = custDe.DueOver30Amt;
            custDe.CustomerClass = Helper.CodeToEnum<CustomerClass>(cusClassCode).ToString();

            IQueryable<InvoiceAging> inv = CommonRep.GetQueryable<InvoiceAging>()
                                           .Where(o => o.LegalEntity == custDe.LegalEntity  //siteCode to LegalEntity
                                               && o.CustomerNum == custDe.CustomerNum
                                               && o.Deal == custDe.Deal) //Deal condition Add 09/14
                                           .Select(o => o);
            custDe.FCollectAmt = inv.Where(o => o.InvoiceDate < agingDT && o.InvoiceDate >= AppContext.Current.User.Now).Sum(o => o.OriginalAmt);
            custDe.FDueOver90Amt = inv.Where(o => o.InvoiceDate < agingDT90).Sum(o => o.OriginalAmt);

            return custDe;
        }
        #endregion

        #region CustomerAging list to commonrep
        /// <summary>
        /// 
        /// </summary>
        /// <param name="custAgs"></param>
        public void AddCustomerAgings(List<CustomerAging> custAgs)
        {
            foreach (CustomerAging custag in custAgs)
            {
                CommonRep.Add(custag);
            }
            CommonRep.Commit();
        }
        #endregion

        #region CustomerAging to commonrep
        /// <summary>
        /// 
        /// </summary>
        /// <param name="cust"></param>
        public void AddCustomer(Customer cust)
        {
            CommonRep.Add(cust);
            CommonRep.Commit();
        }
        #endregion

        #region Get All Customer Data
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IQueryable<Customer> GetCustomer()
        {
            return CommonRep.GetQueryable<Customer>().Where(c => c.Deal == AppContext.Current.User.Deal
                                                        && c.RemoveFlg == "1").Include<Customer,CustomerGroupCfg>(c=>c.CustomerGroupCfg);
        }
        #endregion

        #region Get All MasterData
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public IQueryable<CustomerMasterData> GetCustMasterData()
        {
//            var custs = CommonRep.GetQueryable<Customer>().Where(c => c.Deal == AppContext.Current.User.Deal
//                                            && c.RemoveFlg == "1").Include<Customer, CustomerGroupCfg>(c => c.CustomerGroupCfg);

//            var level = CommonRep.GetQueryable<CustomerLevelView>().Where(c => c.Deal == AppContext.Current.User.Deal);

//            var res = (CommonRep.GetDBContext() as IObjectContextAdapter).ObjectContext
//                .ExecuteStoreQuery<CustomerMasterData>(@"SELECT        A.ID as Id, A.DEAL as Deal, A.CUSTOMER_NUM as CustomerNum, A.CUSTOMER_NAME as CustomerName, A.BILL_GROUP_CODE as BillGroupCode, B.BILL_GROUP_NAME as BillGroupName, A.IS_HOLD_FLG as IsHoldFlg, A.SPECIAL_NOTES as SpecialNotes, A.COLLECTOR as Collector, 
//                         CASE WHEN C.CLASS_LEVEL IS NULL AND C.Risk_level IS NULL THEN 'LVLR' ELSE CONCAT(C.CLASS_LEVEL, C.Risk_level) END AS CLASS
//FROM            dbo.T_CUSTOMER AS A LEFT OUTER JOIN
//                         dbo.T_CUSTOMER_GROUP_CFG AS B ON A.DEAL = B.DEAL AND A.BILL_GROUP_CODE = B.BILL_GROUP_CODE LEFT OUTER JOIN
//                         dbo.V_CUSTOMER_LEVEL AS C ON A.DEAL = C.DEAL AND A.CUSTOMER_NUM = C.CUSTOMER_NUM
//WHERE        (A.REMOVE_FLG = '1')");

//            return res.AsQueryable();

            return CommonRep.GetQueryable<CustomerMasterData>().Where(o => o.Deal == CurrentDeal);
        }
        #endregion

        /// <summary>
        /// MailDetail get ReassignCustomer add by pxc 20160203
        /// </summary>
        /// <param name="mailId"></param>
        /// <returns></returns>
        public IQueryable<CustomerMasterDto> GetCustMasterDataForAssign(string customers)
        {
            //string MessageId = CommonRep.GetDbSet<MailTmp>().Where(m => m.Id == mailId).FirstOrDefault().MessageId;&& cusNums.Contains(m.CustomerNum)
            //List<string> cusNums = CommonRep.GetDbSet<CustomerMail>().Where(m => m.MessageId == messageId).Select(o => o.CustomerNum).ToList<string>();
            if (!string.IsNullOrEmpty(customers))
            {
                List<string> cusNums = customers.Split(',').ToList<string>();
                return GetCustomerMasterForCurrentUser().Where(m => !cusNums.Contains(m.CustomerNum));
            }
            else {
                return GetCustomerMasterForCurrentUser();
            }
        }


        #region Export CustomerMasterData
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public string ExportCustomer(string custnum, string custname, string status, string group, string collector,
                                      string isic, string begintime, string endtime, string miscollector, string misgroup, 
                                       string billcode,string country)
        {
            string templateName = "MasterDataTemplate";
            string custPathName = "CustPathName";
            string tplName = "";
            string pathName = "";
            string fileName = "";
            string virPatnName = "";
            if (custnum == null)
            {
                custnum = "undefined";
            }
            if (custname == null)
            {
                custname = "undefined";
            }
            if (status == "null")
            {
                status = "undefined";
            }
            if (group == null)
            {
                group = "undefined";
            }
            if(billcode==null)
            {
                billcode = "undefined";
            }
            if (collector == null)
            {
                collector = "undefined";
            }
            if (isic == "null")
            {
                isic = "undefined";
            }
            if(country==null)
            {
                country = "undefined";
            }
            if (begintime == null)
            {
                begintime = "undefined";
            }
            if (endtime == null)
            {
                endtime = "undefined";
            }

            IQueryable<CustomerMasterData> exportcust = CommonRep.GetQueryable<CustomerMasterData>().Where(m => m.Deal == CurrentDeal);

            if (custnum != "undefined")
            {
                exportcust = exportcust.Where(m => m.CustomerNum.Contains(custnum));
            }
            if (custname != "undefined")
            {
                exportcust = exportcust.Where(m => m.CustomerName.Contains(custname));
            }
            if (status != "undefined")
            {
                exportcust = exportcust.Where(m => m.IsHoldFlg.Contains(status));
            }
            if (group != "undefined")
            {
                exportcust = exportcust.Where(m => m.BillGroupName.Contains(group));
            }
            if (billcode!="undefined")
            {
                exportcust = exportcust.Where(m=>m.BillGroupCode.Contains(billcode));
            }
            if(collector!="undefined")
            {
                exportcust = exportcust.Where(m=>m.Collector.Contains(collector));
            }
            if(isic!="undefined")
            {
                if (isic == "1")
                {
                    isic = "Y";
                }
                else if(isic=="0"){
                    isic = "N";
                }
                exportcust = exportcust.Where(m => m.IsIc.Contains(isic));
            }
            if (country != "undefined")
            {
                exportcust = exportcust.Where(m => m.Country.Contains(country));
            }
            if (begintime != "undefined")
            {
                DateTime et = Convert.ToDateTime(begintime);
                exportcust = exportcust.Where(m => m.CreateTime > et || m.CreateTime==et);
            }
            if (endtime != "undefined")
            {
                DateTime et = Convert.ToDateTime(endtime);
                exportcust = exportcust.Where(m => m.CreateTime < et || m.CreateTime == et);
            }
            if (miscollector == "true")
            {
                exportcust = exportcust.Where(m => m.Collector == null);
            }
            if (misgroup == "true")
            {
                exportcust = exportcust.Where(m => m.BillGroupCode == null);
            }
            //IQueryable<Customer> exprotcust = custlist.Where(m => m.CustomerNum.Contains(custnum) && m.CustomerName.Contains(custname));
            try
            {
                if (exportcust == null)
                {
                    throw new OTCServiceException("There are no existing datas.");
                }
                
                tplName = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings[templateName].ToString());
                fileName =HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings[custPathName].ToString());
                pathName =HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings[custPathName].ToString() + "MasterDataExport" + AppContext.Current.User.EID + ".xlsx");
                if (Directory.Exists(fileName) == false)
                {
                    Directory.CreateDirectory(fileName);
                }
                WriteToExcel(tplName, pathName, exportcust);

                HttpRequest request = HttpContext.Current.Request;
                StringBuilder appUriBuilder = new StringBuilder(request.Url.Scheme);
                appUriBuilder.Append(Uri.SchemeDelimiter);
                appUriBuilder.Append(request.Url.Authority);
                if (String.Compare(request.ApplicationPath, @"/") != 0)
                {
                    appUriBuilder.Append(request.ApplicationPath);
                }

                virPatnName = appUriBuilder.ToString() + ConfigurationManager.AppSettings[custPathName].ToString().Trim('~') + "MasterDataExport" + AppContext.Current.User.EID + ".xlsx";
                return virPatnName;
            }
            catch (Exception ex)
            {
                Helper.Log.Error("Failed to create Customer Excel!", ex);
                throw;
            }
        }

        #endregion

        #region Export CustomerMasterData
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public void WriteToExcel(string temp, string path, IQueryable<CustomerMasterData> custlist)
        {
            CustomerGroupCfgService service = SpringFactory.GetObjectImpl<CustomerGroupCfgService>("CustomerGroupCfgService");
            IQueryable<CustomerGroupCfg> cfglist = service.GetAllGroups();
            XcceleratorService collecotr = SpringFactory.GetObjectImpl<XcceleratorService>("XcceleratorService");
            int regionId = Convert.ToInt32(AppContext.Current.User.RegionId);
            int centerId = Convert.ToInt32(AppContext.Current.User.CenterId);
            int groupId = Convert.ToInt32(AppContext.Current.User.GroupId);
            int dealId = Convert.ToInt32(AppContext.Current.User.DealId);
            int teamId = Convert.ToInt32(AppContext.Current.User.TeamId);
            IQueryable<SysUser> collectlist = collecotr.GetUsers(regionId, centerId, groupId, dealId, teamId).AsQueryable();
            try
            {
                NpoiHelper helper = new NpoiHelper(temp);
                helper.Save(path, true);
                helper = new NpoiHelper(path);
                string sheetName = "";
                foreach (string sheet in helper.Sheets)
                {
                    sheetName = sheet;

                    if(sheetName=="Customer")
                    {
                      //向sheet为Customer的excel中写入文件
                        helper.ActiveSheetName = sheetName;
                        int rowNo = 1;
                        foreach (var cust in custlist)
                        {
                            helper.SetData(rowNo, 0, cust.Id);
                            helper.SetData(rowNo, 1, cust.CustomerNum);
                            helper.SetData(rowNo, 2, cust.CustomerName);
                            helper.SetData(rowNo, 3, cust.Country);
                            if (cust.ExcludeFlg=="1") 
                            {
                                helper.SetData(rowNo, 4, "Y");
                            }
                            else if (cust.ExcludeFlg == "0")
                            {
                                helper.SetData(rowNo, 4, "N");
                            }
                            else
                            {
                                helper.SetData(rowNo, 4, cust.ExcludeFlg);
                            }
                            helper.SetData(rowNo, 5, cust.Collector);
                            helper.SetData(rowNo, 6, cust.BillGroupCode);
                            helper.SetData(rowNo, 7, cust.BillGroupName);
                         //   helper.SetData(rowNo, 8, cust.SpecialNotes);
                            rowNo++;
                        }
                    }
                    else if (sheetName == "Collector")
                    {
                        //向sheet为Collector的excel中写入文件
                        helper.ActiveSheetName = sheetName;
                        int rowNo = 1;
                        string Team = "";
                        foreach (var collect in collectlist)
                        {
                            CollectorTeam team = GetTeamByEid(collect.EID);
                            if (team == null)
                            {
                                Team = "";
                            }
                            else
                            {
                                Team = GetTeamByEid(collect.EID).Team.TeamName;
                            }
                            helper.SetData(rowNo, 0, collect.EID);
                            helper.SetData(rowNo, 1, collect.Name);
                            helper.SetData(rowNo, 2, collect.Email);
                            helper.SetData(rowNo, 3, Team);
                            rowNo++;
                        }
                    }
                    else if (sheetName == "Group")
                    {
                        helper.ActiveSheetName = sheetName;
                        int rowNo = 1;
                        foreach (var cfg in cfglist)
                        {
                            helper.SetData(rowNo, 0, cfg.BillGroupCode);
                            helper.SetData(rowNo, 1, cfg.BillGroupName);
                            rowNo++;
                        }
                    }
                }
                helper.ActiveSheetName = "Customer";
                //设置sheet
                helper.Save(path, true);
            }
            catch (Exception ex)
            {
                Helper.Log.Error(ex.Message, ex);
                throw ex;
            }
        }
        #endregion
        public string ImportCustHistory()
        {
            FileType fileT = FileType.Customer;
            string archivePath = string.Empty;
            string archiveFileName = string.Empty;
            try
            {
                //upload file to server
                string strMasterDataKey = "ImportMasterData";
                HttpFileCollection files = HttpContext.Current.Request.Files;
                archivePath = ConfigurationManager.AppSettings[strMasterDataKey].ToString();
                archivePath = archivePath + DateTime.Now.Year.ToString() + "\\" + DateTime.Now.Month.ToString();
                if (Directory.Exists(archivePath) == false)
                {
                    Directory.CreateDirectory(archivePath);
                 //   return "Path doesn't exsit！";
                }
                archiveFileName = archivePath + "\\" + DateTime.Now.ToString("yyyy-MM-dd") + "-" + fileT.ToString() +
                            "-" + AppContext.Current.User.EID + "-" + DateTime.Now.ToString("HHmmssf") + ".xlsx";

                FileService service = SpringFactory.GetObjectImpl<FileService>("FileService");
                service.UploadFile(files[0], archiveFileName, fileT);

                return ImportCustomer();
            }
            catch (Exception ex)
            {
                Helper.Log.Error(ex.Message, ex);
                throw new OTCServiceException("Uploaded file failed!");
            }
        }
        public string ImportCustomer() 
        {
            string strCode = "";
            FileUploadHistory fileUpHis = new FileUploadHistory();
            FileService fileService = SpringFactory.GetObjectImpl<FileService>("FileService");

            XcceleratorService collecotr = SpringFactory.GetObjectImpl<XcceleratorService>("XcceleratorService");
            int regionId = Convert.ToInt32(AppContext.Current.User.RegionId);
            int centerId = Convert.ToInt32(AppContext.Current.User.CenterId);
            int groupId = Convert.ToInt32(AppContext.Current.User.GroupId);
            int dealId = Convert.ToInt32(AppContext.Current.User.DealId);
            int teamId = Convert.ToInt32(AppContext.Current.User.TeamId);
            IQueryable<SysUser> collectlist = collecotr.GetUsers(regionId, centerId, groupId, dealId, teamId).AsQueryable();

            try 
            {
                strCode = Helper.EnumToCode<FileType>(FileType.Customer);
                fileUpHis = fileService.GetSuccessData(strCode);
                string strpath = "";
                IQueryable<Customer> custlist = GetCustomer();
                Customer cust=new Customer();
                //Customer cust = new Customer();
                if (fileUpHis == null)
                {
                    throw new Exception("import file is not found!");
                }
                strpath = fileUpHis.ArchiveFileName;
                NpoiHelper helper = new NpoiHelper(strpath);
                string sheetName = "";
                sheetName = "Customer";
                helper.ActiveSheetName = sheetName;
                int i=1;
                //added by zhangYu 20160128
                List<Customer> listcust =new List<Customer>();

                //when excel have one row
                while (helper.GetValue(i, 1)!=null)
                {
                    //get value from excel
                    var num=helper.GetValue(i, 1).ToString();
                    cust = custlist.Where(o => o.CustomerNum ==num).FirstOrDefault();
                    if (cust == null)
                    {
                        Customer newcust = new Customer();
                        newcust.CustomerNum = helper.GetValue(i, 1).ToString();
                        newcust.CustomerName = helper.GetValue(i, 2).ToString();
                        newcust.CreateTime = AppContext.Current.User.Now;
                        newcust.IsHoldFlg = "0";
             //         newcust.Country = helper.GetValue(i, 3) == null ? null : helper.GetValue(i, 3).ToString();
                        if (helper.GetValue(i, 4)==null)
                        {
                            newcust.ExcludeFlg = "0";
                        }
                        else if (helper.GetValue(i, 4).ToString()=="Y")
                        {
                            newcust.ExcludeFlg = "1";
                        }
                        else if (helper.GetValue(i, 4).ToString() == "N")
                        {
                            newcust.ExcludeFlg="0";
                        }
                        if (helper.GetValue(i, 5) != null)
                        {
                            string collectEid = helper.GetValue(i, 5).ToString();
                            SysUser collect = collectlist.Where(o => o.EID == collectEid).FirstOrDefault();
                            if (collect != null)
                            {
                                newcust.Collector = collect.EID;
                                newcust.CollectorName = collect.Name;
                            }
                        }

                        if (helper.GetValue(i, 6) == null)
                        {
                            newcust.BillGroupCode = null;
                        }
                        else
                        {
                            var code = helper.GetValue(i, 6).ToString();
                            CustomerGroupCfgService service = SpringFactory.GetObjectImpl<CustomerGroupCfgService>("CustomerGroupCfgService");
                            CustomerGroupCfg group = service.GetGroupByCode(code);
                            if (group == null)
                            {
                                CustomerGroupCfg cfg = new CustomerGroupCfg();
                                cfg.BillGroupCode = helper.GetValue(i, 6).ToString();
                                cfg.BillGroupName = helper.GetValue(i, 7) == null ? null : helper.GetValue(i, 7).ToString();
                                cfg.Deal = AppContext.Current.User.Deal;
                                cfg.CreateTime = AppContext.Current.User.Now;
                                CommonRep.Add(cfg);
                                CommonRep.Commit();
                                newcust.BillGroupCode = helper.GetValue(i, 6).ToString();
                            }
                            else
                            {
                                newcust.BillGroupCode = helper.GetValue(i, 6).ToString();
                            }
                        }
                        newcust.Operator = AppContext.Current.User.EID;
               //         newcust.SpecialNotes = helper.GetValue(i, 8) == null ? null : helper.GetValue(i, 8).ToString();
                        newcust.Deal = AppContext.Current.User.Deal;
                        newcust.RemoveFlg = "1";
                        ////added by zhangYu 20160128 optimization Start
                        listcust.Add(newcust);
                        //CommonRep.Add(newcust);
                        //CommonRep.Commit();
                        ////added by zhangYu 20160128 optimization End
                    }
                    else 
                    {
                        var old = cust;
          //            cust.CustomerName = helper.GetValue(i, 2).ToString();
          //            cust.Country = helper.GetValue(i, 3) == null ? null : helper.GetValue(i, 3).ToString();
          //            cust.ExcludeFlg = helper.GetValue(i, 4) == null ? null : Convert.ToInt32(helper.GetValue(i, 4)).ToString();
                        cust.UpdateTime = AppContext.Current.User.Now;

                        if (cust.IsHoldFlg == "1")
                        {
                            cust.IsHoldFlg = "1";
                        }else {
                            cust.IsHoldFlg = "0";
                        }
                        if (helper.GetValue(i, 4) == null)
                        {
                            cust.ExcludeFlg = "0";
                        }
                        else if (helper.GetValue(i, 4).ToString() == "Y")
                        {
                            cust.ExcludeFlg = "1";
                        }
                        else if (helper.GetValue(i, 4).ToString() == "N")
                        {
                            cust.ExcludeFlg = "0";
                        }
                   //   cust.Collector = helper.GetValue(i, 5) == null ? null : helper.GetValue(i, 5).ToString();
                        if (helper.GetValue(i, 5) != null)
                        {
                              string collectEid=helper.GetValue(i, 5).ToString();
                              SysUser collect=collectlist.Where(o => o.EID == collectEid).FirstOrDefault();
                              if (collect != null)
                              {
                                  cust.Collector = collect.EID;
                                  cust.CollectorName = collect.Name;
                              }
                        }


                       // jugde BillGroup exsit if exsit get value from excel else add code into CustomerGroupCfg
                        if (helper.GetValue(i, 6) == null)
                        {
                            cust.BillGroupCode = null;
                        }
                        else
                        {
                            var code = helper.GetValue(i, 6).ToString();
                            CustomerGroupCfgService service = SpringFactory.GetObjectImpl<CustomerGroupCfgService>("CustomerGroupCfgService");
                            CustomerGroupCfg group=service.GetGroupByCode(code);
                            if (group == null)
                            {
                                CustomerGroupCfg cfg = new CustomerGroupCfg();
                                cfg.CreateTime = AppContext.Current.User.Now;
                                cfg.BillGroupCode = helper.GetValue(i, 6).ToString();
                                cfg.BillGroupName = helper.GetValue(i, 7) == null ? null : helper.GetValue(i, 7).ToString();
                                cfg.Deal = AppContext.Current.User.Deal;
                                CommonRep.Add(cfg);
                                CommonRep.Commit();
                                cust.BillGroupCode = helper.GetValue(i, 6).ToString();
                            }
                            else {
                                cust.BillGroupCode = helper.GetValue(i, 6).ToString();
                            }
                        }
                        cust.Operator = AppContext.Current.User.EID;
             //           cust.SpecialNotes = helper.GetValue(i, 8) == null ? null : helper.GetValue(i, 8).ToString();
                        ObjectHelper.CopyObjectWithUnNeed(cust, old, new string[] { "Id", "CustomerNum" });
                        ////added by zhangYu 20160128 optimization Start
                        //CommonRep.Commit();
                        //added by zhangYu 20160128 optimization End
                    }
                    i = i + 1;
                }
                //added by zhangYu 20160128 optimization Start
                //using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 30, 0)))
                //{
                //    CommonRep.Commit();
                //    (CommonRep.GetDBContext() as OTCEntities).BulkInsert(listcust);
                //    scope.Complete();
                //}
                //added by zhangYu 20160128 optimization End
                CommonRep.AddRange(listcust);
                CommonRep.Commit();
                return "Import Finished!";
            }
            catch (Exception ex)
            {
                fileUpHis.ProcessFlag = Helper.EnumToCode<UploadStates>(UploadStates.Failed);
                fileService.CommonRep.Commit();
                Helper.Log.Error(ex.Message, ex);
                throw ex;
            }
        }

        public string AddCustMasterData(Customer cust) {
            try
            {
                if (string.IsNullOrEmpty(cust.CustomerName))
                {
                    return "Please Add Customer Name!";
                }
                cust.Deal = AppContext.Current.User.Deal.ToString();
                cust.CreateTime = AppContext.Current.User.Now;
                cust.RemoveFlg = "1";
                cust.IsHoldFlg = "0";
                Customer custflg = GetOneCustomer(cust.CustomerNum);
                if (custflg == null)
                {
                    if (cust.Id == 0)
                    {
                        CommonRep.Add(cust);
                        CommonRep.Commit();
                        return "Add Success!";
                    }
                    else
                    {
                        Customer old = CommonRep.FindBy<Customer>(cust.Id);
                        ObjectHelper.CopyObjectWithUnNeed(cust, old, new string[] { "Id" });
                        CommonRep.Commit();
                        return "Update Success！";
                    }
                    //service.CommonRep.Commit();
                }
                else
                {
                    return "This customer already exsit!";
                }
            }
            catch (Exception ex)
            {
                Helper.Log.Info(ex);
                throw new Exception(ex.Message);
            }
        }
        public string UpdateCustMasterData(Customer cust) {
            try
            {
                cust.Deal = AppContext.Current.User.Deal.ToString();
                cust.UpdateTime = AppContext.Current.User.Now;
                if (cust.RemoveFlg.Equals("0"))
                {
                    if (checkDelCustomer(cust))
                    {
                        Helper.Log.Info(cust);
                        Customer old = CommonRep.FindBy<Customer>(cust.Id);
                        ObjectHelper.CopyObjectWithUnNeed(cust, old, new string[] { "Id", "Contacter", "CustomerGroupCfg" });
                        CommonRep.Commit();
                        return "Delete Success!";
                    }
                    return "Current Customer Exsit Some Information";
                }
                else
                {
                    Helper.Log.Info(cust);
                    if (cust.BillGroupCode == "")
                    {
                        cust.BillGroupCode = null;
                    }
                    Customer old = CommonRep.FindBy<Customer>(cust.Id);
                    ObjectHelper.CopyObjectWithUnNeed(cust, old, new string[] { "Id", "Contacter", "CustomerGroupCfg" });
                    CommonRep.Commit();
                    return "Update Success!";
                }
            }
            catch (Exception ex)
            {
                Helper.Log.Info(ex);
                throw new Exception(ex.Message);
            }
        }


        #region get one customer by customernum and sitecode
        //add by pxc 20150722
        /// <summary>
        /// 
        /// </summary>
        /// <param name="num"></param>
        /// <param name="site"></param>
        /// <returns></returns>
        public Customer GetOneCustomer(string num)
        {
            return CommonRep.GetDbSet<Customer>().Where(m => m.CustomerNum == num && m.Deal == AppContext.Current.User.Deal).FirstOrDefault();
        }
        #endregion

        #region get customer by customer number
        //add by pxc 20150722
        /// <summary>
        /// 
        /// </summary>
        /// <param name="num"></param>
        /// <param name="site"></param>
        /// <returns></returns>
        public List<Customer> GetCustomerByCustomerNum(string cusNum)
        {
            return CommonRep.GetDbSet<Customer>().Where(m => m.CustomerNum == cusNum && m.Deal == AppContext.Current.User.Deal).ToList();
        }
        #endregion

        #region Get All Group Data from cache
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public List<CustomerGroupCfg> GetAllCustomerGroup()
        {
            return CacheSvr.GetOrSet<List<CustomerGroupCfg>>("Cache_CustomerGroupCfg", () =>
            {
                return CommonRep.GetQueryable<CustomerGroupCfg>().ToList();
            });
        }
        #endregion

        public List<CustomerGroupCfg> GetAllCustomerGroupFromDb()
        {
             return CommonRep.GetQueryable<CustomerGroupCfg>().ToList();
        }

        public List<CustomerGroupCfgHistory> GetAllCustomerGroupHis()
        {
           return CommonRep.GetQueryable<CustomerGroupCfgHistory>().ToList();
        }

        #region data uploading
        /// <summary>
        /// 
        /// </summary>
        public void allFileImport()
        {
            FileUploadHistory accFileName;
            FileUploadHistory invFileName;
            StreamReader srAcc;
            StreamReader srInv;
            DateTime? dt;
            DateTime? dtReport;
            UploadStates sts;
            bool isSuc;
            string strGuid;
            string msg = "";

            string strSite;
            //init variable
            strMessage = string.Empty;
            srAcc = null;
            srInv = null;
            //strSiteAcc = string.Empty;
            //strSiteInv = string.Empty;
            strSite = null;
            dt = null;
            sts = UploadStates.Failed;
            accFileName = new FileUploadHistory();
            invFileName = new FileUploadHistory();
            isSuc = false;
            strGuid = string.Empty;

            FileService fileService = SpringFactory.GetObjectImpl<FileService>("FileService");

            try
            {

                //get filefullname
                string strCode;
                strCode = Helper.EnumToCode<FileType>(FileType.Account);
                accFileName = fileService.GetNewestData(strCode);
                strCode = Helper.EnumToCode<FileType>(FileType.Invoice);
                invFileName = fileService.GetNewestData(strCode);
                if (accFileName == null || invFileName == null)
                {
                    //file not found
                    strMessage = "Both Account level And invoice level are required!" + strEnter;
                }
                else
                {
                    //file open
                    srAcc = fileOpen(accFileName.ArchiveFileName);
                    srInv = fileOpen(invFileName.ArchiveFileName);
                    //site check
                    strSite = strSiteGet(srAcc, srInv, accFileName.OriginalFileName, invFileName.OriginalFileName);
                     if (string.IsNullOrEmpty(strSite))
                    {
                        //Do nothing
                    }
                    else
                    {
                        strMessage = string.Empty;
                        dt = formartCheck(srAcc,out msg);
                        if (dt == null)
                        {
                            strMessage = accFileName.OriginalFileName + " Bad file format!" + strEnter + msg + strEnter;
                        }
                        else
                        {
                            strGuid = System.Guid.NewGuid().ToString("N");
                            dtReport = fileService.GetFileUploadHistory().Where(o => o.LegalEntity == strSite).Max(o => o.ReportTime);
                            if (dt.Value > CurrentTime || dt < dtReport)
                            {
                                strMessage = accFileName.OriginalFileName + " Report is old!" + strEnter + "(Report Date is wrong!)" + strEnter;
                            }
                            else if (!invoiceFormartCheck(srInv))
                            {
                                strMessage = invFileName.OriginalFileName + " Bad file format!" + strEnter;
                            }
                            else
                            {
                                dataImport(srAcc, strSite, strGuid);
                                dataInvoiceImport(srInv, strSite, strGuid);
                                if (listAgingStaging.Count > 0 && invoiceAgingList.Count > 0)
                                {
                                    if (isSameCust())
                                    {
                                        //exCustInfo(accFileName.OriginalFileName, invFileName.OriginalFileName, strGuid);
                                        dataAddToComm(strSite);

                                        sts = UploadStates.Success;
                                        isSuc = true;
                                    }
                                    else
                                    {
                                        strMessage = "Account level's Customers and invoice level's Customers are inconformity!" + strEnter;
                                    }
                                }
                                else
                                {
                                    strMessage = "Import Data is empty!" + strEnter;
                                }
                            }
                        }
                    }
                }

                if (!string.IsNullOrEmpty(strMessage))
                {
                    strGuid = null;
                    throw new AgingImportException(strMessage, isSuc);
                }

                fileService.upLoadHisUp(accFileName, invFileName, sts, dt, strSite, listAgingStaging.Count, invoiceAgingList.Count, strGuid);
            }
            catch (AgingImportException ex)
            {
                fileService.upLoadHisUp(accFileName, invFileName, sts, null);
                throw new AgingImportException(ex.Message, isSuc);
            }
            catch (Exception ex)
            {
                fileService.upLoadHisUp(accFileName, invFileName, sts, null);
                throw new AgingImportException(ex.Message, isSuc);
            }
            finally
            {
                if (srAcc != null)
                {
                    srAcc.Dispose();
                    srAcc.Close();
                }
                if (srInv != null)
                {
                    srInv.Dispose();
                    srInv.Close();
                }
            }

        }
        #endregion

        #region acc inv customer check
        private bool isSameCust()
        {
            //List<string> lacc = new List<string>();
            //List<string> linv = new List<string>();
            bool rtn;
            rtn = true;
            List<CustomerAgingStaging> agingStagingListNew = new List<CustomerAgingStaging>();
            List<InvoiceAgingStaging> invoiceAgingListNew = new List<InvoiceAgingStaging>();
            if (listAgingStaging == null || invoiceAgingList == null)
            {
                rtn = false;
            }

            IList<CustomerAgingStaging> custaing = getAmtNotEqualAging(out invoiceAgingListNew).ToList();


            agingStagingListNew = (from acc in listAgingStaging
                                   join inv in invoiceAgingListNew
                                   on acc.CustomerNum equals inv.CustomerNum
                                   into dft
                                   from invdft in dft.DefaultIfEmpty()
                                   where invdft == null
                                   select new CustomerAgingStaging
                                   {
                                       Deal = acc.Deal,
                                       LegalEntity = acc.LegalEntity,
                                       CustomerNum = acc.CustomerNum,
                                       TotalAmt = acc.TotalAmt
                                   }).ToList();

            listAgingStaging.ForEach(aging =>
            {
                if (custaing.Where(o => o.LegalEntity == aging.LegalEntity && o.CustomerNum == aging.CustomerNum).ToList().Count > 0)
                {
                    aging.AmtMappingFlg = "1";
                }
                else
                {
                    aging.AmtMappingFlg = "0";
                }
                if (agingStagingListNew.Where(o => o.LegalEntity == aging.LegalEntity
                                            && o.CustomerNum == aging.CustomerNum).ToList().Count > 0)
                {
                    aging.MissInvioceFlg = "1";
                }
                else
                {
                    aging.MissInvioceFlg = "0";
                }
            });

            invoiceAgingListNew = (from inv in invoiceAgingListNew
                                   join acc in listAgingStaging
                                   on inv.CustomerNum equals acc.CustomerNum
                                   into dft
                                   from accdft in dft.DefaultIfEmpty()
                                   where accdft == null
                                   select new InvoiceAgingStaging
                                   {
                                       Deal = inv.Deal,
                                       LegalEntity = inv.LegalEntity,
                                       CustomerNum = inv.CustomerNum,
                                       BalanceAmt = inv.BalanceAmt
                                   }).ToList();

            invoiceAgingList.ForEach(aging =>
            {
                if (invoiceAgingListNew.Where(o => o.LegalEntity == aging.LegalEntity 
                                                && o.CustomerNum == aging.CustomerNum
                                                && o.BalanceAmt != 0).ToList().Count > 0)
                {
                    aging.MissAccountFlg = "1";
                }
                else
                {
                    aging.MissAccountFlg = "0";
                }
            });

            //if (agingStagingListNew.Count == 0 && invoiceAgingListNew.Count == 0)
            //{
            //    rtn = true;
            //}
            //else 
            //{
            //    rtn = true;
            //    //if (agingStagingListNew.Count > 0)
            //    //{
            //    //    if (agingStagingListNew.Max(o => o.TotalAmt) != 0 || agingStagingListNew.Sum(o => o.TotalAmt) != 0)
            //    //    {
            //    //        rtn = false;
            //    //    }
            //    //}
            //    if (invoiceAgingListNew.Count > 0)
            //    {
            //        if (invoiceAgingListNew.Max(o => o.BalanceAmt) != 0 || invoiceAgingListNew.Sum(o => o.BalanceAmt) != 0)
            //        {
            //            rtn = false;
            //        }
            //    }
            //}
            

            //need check 
            /*
            invoiceAgingListNew = (from inv in invoiceAgingList
                                   group inv by inv.InvoiceNum
                                   into invg
                                   select new InvoiceAgingStaging
                                    {
                                       InvoiceNum = invg.Key,
                                       Id = invg.Count(),
                                       Deal = invg.Max(o=>o.Deal),
                                       CustomerNum = invg.Max(o=>o.CustomerNum),
                                       LegalEntity = invg.Max(o=>o.LegalEntity)
                                    }).ToList();


            invoiceAgingList = (from inv in invoiceAgingList
                                join invNew in invoiceAgingListNew
                                on new { inv.Deal, inv.CustomerNum, inv.LegalEntity, inv.InvoiceNum }
                                    equals new { invNew.Deal, invNew.CustomerNum, invNew.LegalEntity, invNew.InvoiceNum }
                                where invNew.Id == 1
                                select inv).ToList();*/

            //delete:total amt is 0 and no invioce
            //listAgingStaging = listAgingStaging.FindAll(o => o.MissInvioceFlg != "1" || o.TotalAmt != 0);
            listAgingStaging = listAgingStaging.FindAll(o => o.MissInvioceFlg != "1");

            agingStagingListNew.Clear();
            custaing.Clear();
            invoiceAgingListNew.Clear();

            return rtn;
        }

        #endregion
        #region site check
        /// <summary>
        /// 
        /// </summary>
        /// <param name="srAcc"></param>
        /// <param name="srInv"></param>
        /// <returns></returns>
        private string strSiteGet(StreamReader srAcc, StreamReader srInv, string fileNameAcc, string fileNameInv)
        {
            string rtnSite;
            rtnSite = strSiteGetFromFile(srAcc, 0, fileNameAcc);
            if (!strSiteGetFromName(rtnSite, fileNameInv))
            {
                rtnSite = string.Empty;
                strMessage = "Please upload reports with same legal entity!" + strEnter;
            }
            //if (rtnSite == strSiteGetFromFile(srInv, 1, fileNameInv))
            //{
            //}
            //else
            //{
            //    if (string.IsNullOrEmpty(strMessage))
            //    {
            //        strMessage += "Account level's Legal Entity and invoice level's Legal Entity are inconformity!" + strEnter;
            //    }
            //    rtnSite = string.Empty;
            //}

            return rtnSite;
        }

        private bool strSiteGetFromName(string site,string fileName)
        {
            List<SysTypeDetail> deList = BDService.GetSysTypeDetail("030");
            string strEndChar = deList.Where(o => o.DetailValue == site).Select(o => o.DetailName).FirstOrDefault().ToString();

            string fullname = System.IO.Path.GetFileNameWithoutExtension(fileName);

            if (fullname.EndsWith(strEndChar))
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sr"></param>
        /// <param name="iFlg"></param>
        /// <returns></returns>
        private string strSiteGetFromFile(StreamReader sr, int iFlg,string strfileName)
        {
            string rtnSite;
            string orgSite;
            orgSite = string.Empty;
            while ((rtnSite = sr.ReadLine()) != null)
            {
                if (string.IsNullOrEmpty(rtnSite))
                {
                    strMessage += strfileName + ": Bad file format!"+ strEnter +"(Legal Entity is empty!)" + strEnter;
                    rtnSite = string.Empty;
                }
                else
                {
                    //if (iFlg == 1)
                    //{
                    //    orgSite = rtnSite;
                    //    rtnSite = rtnSite.Split(strInvSplit)[0].Trim();
                    //}
                    //else
                    //{
                    orgSite = rtnSite;
                    //}
                    rtnSite = siteGet(rtnSite);
                    if (string.IsNullOrEmpty(rtnSite))
                    {
                        strMessage += strfileName + "'s Legal Entity:[" + orgSite + "] is not found in System!" + strEnter;
                    }
                }
                break;
            }
            return rtnSite;
        }
        #endregion

        public void dataAddToComm(string strSite)
        {
            List<InvoiceAgingStaging> list = new List<InvoiceAgingStaging>();
            try
            {
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 30, 0)))
                {
                    string DelSql;
                    DelSql = "delete from T_CUSTOMER_AGING_STAGING WHERE DEAL = '"
                               + CurrentDeal + "' AND LEGAL_ENTITY = '" + strSite + "';";
                    CommonRep.GetDBContext().Database.ExecuteSqlCommand(DelSql);

                    DelSql = "delete from T_INVOICE_AGING_STAGING WHERE DEAL = '"
                               + CurrentDeal + "' AND LEGAL_ENTITY = '" + strSite + "';";
                    CommonRep.GetDBContext().Database.ExecuteSqlCommand(DelSql);

                    CommonRep.AddRange(custList.FindAll(cust => cust.Id == 0));
                    CommonRep.Commit();

                    (CommonRep.GetDBContext() as OTCEntities).BulkInsert(listAgingStaging);
                    (CommonRep.GetDBContext() as OTCEntities).BulkInsert(invoiceAgingList);

                    scope.Complete();
                }
            }
            catch (Exception ex)
            {
                if (ex.GetType().Name == typeof(DbEntityValidationException).Name)
                {
                    if ((ex as DbEntityValidationException).EntityValidationErrors != null)
                    { 

                    }
                }
                Helper.Log.Error(ex.Message, ex);
                throw new AgingImportException("Failed to save customer level aging data!", ex);
            }
        }

        /*
        #region find exsist customer
        public void exCustInfo(string strfileName1, string strfileName2, string strImpID)
        {
            CustomerAgingStagingHistory custHis;
            string strSubMessage;
            bool isEx;
            isEx = false;
            listAgingStagingEx = new List<CustomerAgingStaging>();
            listAgingStagingHis = new List<CustomerAgingStagingHistory>();
            listAgingStagingEx = GetCustomerAgingStaging().ToList();
            strSubMessage = string.Empty;

            listAgingStaging.ForEach(aging =>
            {
                if (CustAgingExist(aging.SiteCode, aging.CustomerNum, AppContext.Current.User.EID))
                {
                    strSubMessage += "Legal Entity:[" + aging.SiteCode
                                    + "],Customer Code:[" + aging.CustomerNum.ToString()
                                    + "],Upload Operator:["
                                    + AppContext.Current.User.EID.ToString() + "]" + strEnter;
                    isEx = true;
                    //为了在导入信息里面删除已经存在的cust，aging.Id设置成0
                    aging.Id = 0;
                }
                else
                {
                    custHis = new CustomerAgingStagingHistory();
                    ObjectHelper.CopyObjectWithUnNeed(aging, custHis, new string[] { "Id", "ImportId" });
                    custHis.ImportId = strImpID;
                    listAgingStagingHis.Add(custHis);
                }
            });

            //删除掉已经存在的cust
            listAgingStaging = listAgingStaging.FindAll(cust => cust.Id != 0);

            if (isEx)
            {
                strMessage = "Customer level aging existing for:" + strEnter +
                                strSubMessage + " in the uploading file:" + strfileName1 + " And " + strfileName2
                                + strEnter;
            }

        }
        #endregion
        */

        /// <summary>
        /// file open by filefullname
        /// </summary>
        /// <param name="strFileName"></param>
        private StreamReader fileOpen(string strFileName)
        {
            return new StreamReader(strFileName, Encoding.Default);
        }

        private string getTerm(string strTerm)
        {
            string rtn;
            if (string.IsNullOrEmpty(strTerm))
            {
                rtn = strTerm;
            }
            else if (strTerm.ToUpper().IndexOf("CIA") >= 0)
            {
                rtn = "0";
            }
            else if (strTerm.ToUpper().IndexOf("N00") >= 0)
            {
                rtn = "0";
            }
            else if (strTerm.ToUpper().IndexOf("COD") >= 0)
            {
                rtn = "0";
            }
            else
            {
                rtn = strTerm.ToUpper().Replace("NET","");
            }
            return rtn;
        }

        /// <summary>
        /// AccountLevel导入
        /// </summary>
        private void dataImport(StreamReader sr, string strSite, string strGuid)
        {
            string line;//数据行读取用
            string[] arrData;
            CustomerAgingStaging custaging;
            custList = new List<Customer>();
            listAgingStaging = new List<CustomerAgingStaging>();
            int intCount;

            intCount = 0;

            List<Customer> allCustList = new List<Customer>();
            CustomerTeam custTeam = new CustomerTeam();
            Customer cust = new Customer();
            List<CustomerTeam> AllCustTeamList;
            AllCustTeamList = GetCustomerTeam().ToList();
            //List<Customer> custolds = CommonRep.GetDbSet<Customer>().Where(c => c.Deal == AppContext.Current.User.Deal
            //                        && c.RemoveFlg == "0").ToList();

            allCustList = GetCustomer().ToList();

            while ((line = sr.ReadLine()) != null)
            {
                arrData = line.Split(strSplit);
                if (!dataCheck(arrData))
                {
                    continue;
                }
                if (arrData[0] == strEnd)
                {
                    break;
                }
                intCount++;
                try
                {

                    if (!TryGetCustomer(arrData[1], arrData[0], allCustList, custList, AllCustTeamList, out cust, out custTeam))
                    {
                        cust = new Customer()
                        {
                            CustomerNum = arrData[1],
                            CustomerName = arrData[0],
                            //BillGroupCode = "",
                            Deal = CurrentDeal,
                            Country = arrData[6],
                            //0:No
                            IsHoldFlg = "0",
                            AutoReminderFlg = "0",
                            //ExcludeFlg = "0",
                            ExcludeFlg = getNewICCust(arrData[0]),//11/03 update Ic logic add
                            //0:Not Assign
                            RemoveFlg = "1",
                            CreateTime = CurrentTime //11/03 Add create time Add
                        };
                    }
                    //Customer custold = custolds.Where(o => o.CustomerNum == cust.CustomerNum && o.Deal == cust.Deal).FirstOrDefault();
                    //if (custold != null)
                    //{
                    //    ObjectHelper.CopyObjectWithUnNeed(cust, custold, new string[] { "Id" });
                    //    cust.Id = custold.Id;
                    //}
                    custList.Add(cust);
                    if (cust.ExcludeFlg == "1")
                    {
                        continue;
                    }
                    custaging = new CustomerAgingStaging();
                    custaging.Deal = CurrentDeal;
                    custaging.LegalEntity = strSite;
                    custaging.CustomerNum = arrData[1];
                    //CustomerInfo取得
                    //custaging.CustomerName = arrData[0];
                    custaging.CustomerName = cust.CustomerName;
                    //BillGrpCode取得
                    custaging.BillGroupCode = cust.BillGroupCode;
                    if (cust.Id > 0)
                    {
                        custaging.BillGroupName = GetAllCustomerGroup().Where(o => o.BillGroupCode == cust.BillGroupCode)
                                                    .Select(o => o.BillGroupName).FirstOrDefault();
                    }
                    else
                    {
                        custaging.BillGroupName = "";
                    }
                    //Country做成必要待确认
                    custaging.Country = arrData[6];
                    //CustomerInfo取得
                    custaging.CreditTerm = getTerm(arrData[3]);// cust.CreditTrem;
                    custaging.CreditLimit = dataConvertToDec(arrData[4]);
                    //custaging.CreditLimit =  cust.CreditLimit;
                    if (custTeam != null)
                    {
                        custaging.Collector = custTeam.Collector;
                    }

                    custaging.CollectorSys = arrData[7];
                    //get total amt from txt file
                    //custaging.TotalAmt = amtGet(arrData, 0, 0);
                    //custaging.TotalAmt = dataConvertToDec(arrData[8]);
                    custaging.CurrentAmt = dataConvertToDec(arrData[9]);
                    custaging.Due30Amt = dataConvertToDec(arrData[10]);
                    custaging.Due60Amt = dataConvertToDec(arrData[11]);
                    custaging.Due90Amt = dataConvertToDec(arrData[12]);
                    custaging.Due120Amt = dataConvertToDec(arrData[13]);
                    custaging.Due150Amt = dataConvertToDec(arrData[14]);
                    custaging.Due180Amt = dataConvertToDec(arrData[15]);
                    custaging.Due210Amt = dataConvertToDec(arrData[16]);
                    custaging.Due240Amt = dataConvertToDec(arrData[17]);
                    custaging.Due270Amt = dataConvertToDec(arrData[18]);
                    custaging.Due300Amt = dataConvertToDec(arrData[19]);
                    custaging.Due330Amt = dataConvertToDec(arrData[20]);
                    custaging.Due360Amt = dataConvertToDec(arrData[21]);
                    custaging.DueOver360Amt = dataConvertToDec(arrData[22]);
                    custaging.TotalAmt = custaging.CurrentAmt + custaging.Due30Amt +
                                        custaging.Due60Amt + custaging.Due90Amt +
                                        custaging.Due120Amt +custaging.Due150Amt +
                                        custaging.Due180Amt + custaging.Due210Amt +
                                        custaging.Due240Amt + custaging.Due270Amt +
                                        custaging.Due300Amt +custaging.Due330Amt +
                                        custaging.Due360Amt + custaging.DueOver360Amt;
                    //added by gai 07/23
                    //custaging.IsHoldFlg = cust.IsHoldFlg;
                    custaging.AccountStatus = strAccountStats;
                    custaging.CreateDate = AppContext.Current.User.Now;
                    custaging.Operator = AppContext.Current.User.EID;
                    custaging.Sales = cust.Sales;
                    custaging.ImportId = strGuid;
                    //custaging.RiskScore = cust.RScore.ToString();
                    custaging.IsHoldFlg = cust.IsHoldFlg == null ? "0" : cust.IsHoldFlg;
                    
                    //upload all the col
                    custaging.Currency = arrData[2];
                    custaging.CountryCode = arrData[5];
                    custaging.OutstandingAmt = dataConvertToDec(arrData[8]);
                    custaging.CityOrState = arrData[23];
                    custaging.ContactName = arrData[24];
                    custaging.ContactPhone = arrData[25];
                    custaging.CustomerCreditMemo = arrData[26];
                    custaging.CustomerPayments = arrData[27];
                    custaging.CustomerReceiptsAtRisk = arrData[28];
                    custaging.CustomerClaims = arrData[29];
                    custaging.CustomerBalance = dataConvertToDec(arrData[30]);

                    // custClass change
                    //custaging.CustomerClass = cust.VClass + cust.RClass;
                    //added by zhangYu NFCusFlg  upload the customer notFind in customerTable
                    if (cust.Id == 0)
                    {
                        custaging.CusExFlg = "0";
                    }
                    else
                    {
                        custaging.CusExFlg = "1";
                    }

                    if (cust.ExcludeFlg == "1")
                    {
                        continue;
                    }
                    listAgingStaging.Add(custaging);
                }
                catch (Exception ex)
                {
                    Helper.Log.Error(ex.Message, ex);
                    throw new AgingImportException("Failed to extract customer level aging data!");
                }
            }

        }
        /// <summary>
        /// //11/03 Ic logic add
        /// </summary>
        /// <param name="custName"></param>
        /// <returns></returns>
        private string getNewICCust(string custName)
        {
            string rtn = "0";
            string strICs= BDService.GetSysConfigByCode("006").CfgValue;
            string[] strIC;
            strIC = strICs.Split('^');

            foreach (string str in strIC)
            {
                if (custName.ToUpper().IndexOf(str.ToUpper())>=0)
                {
                    rtn = "1";
                    break;
                }
            }

            return rtn;
            
        }

        /// <summary>
        /// Format Check
        /// </summary>
        /// <returns></returns>
        private DateTime? formartCheck(StreamReader sr,out string msg)
        {
            string line;//数据行读取用
            int i;
            DateTime? dt;
            string[] strSplited;

            i = 0;
            dt = null;

            string msg1 = "(" + strFormatCheck + " is not found!)" + strEnter;
            string msg2 = "(Report Date is not found!)" + strEnter;

            msg = "";

            while ((line = sr.ReadLine()) != null)
            {
                if (line == strFormatCheck)
                {
                    i++;
                    msg1 = "";
                    continue;
                }
                strSplited = line.Split(strSplit);
                if (strSplited[0] == strDtCheck && strSplited.Length == 2)
                {
                    i++;
                    dt = dataConvertToDT(strSplited[1]);
                    msg2 = "";
                    continue;
                }
                if (strSplited.Length == intAccountCols && strSplited[0] == strHeaderCheck)
                {
                    i++;
                    break;
                }
            }
            if (i < 3)
            {
                msg = msg1 + msg2;
                dt = null;
            }
            return dt;
        }

        /// <summary>
        /// Format Check
        /// </summary>
        /// <param name="strCheck"></param>
        /// <returns></returns>
        private bool invoiceFormartCheck(StreamReader sr)
        {
            string line;//数据行读取用
            int i;
            bool isHeaderCheck;

            i = 0;
            isHeaderCheck = false;
            while ((line = sr.ReadLine()) != null)
            {
                i++;
                if (line.Split(strSplit)[0] == strInvoiceHeaderCheck && line.Split(strSplit).Length == intInvoiceCols)
                {
                    return true;
                }
            }
            return isHeaderCheck;
        }

        /// <summary>
        /// Format Check
        /// </summary>
        /// <param name="strCheck"></param>
        /// <returns></returns>
        private bool subFormatCheck(string strCheck)
        {
            if (strCheck == strFormatCheck)
            {
                return true;
            }
            return false;
        }

        /// <summary>
        /// 导入必要data判断
        /// </summary>
        /// <param name="arrData"></param>
        /// <returns></returns>
        private bool dataCheck(string[] arrData)
        {
            string strCheck;

            if (arrData.Length != intAccountCols)
            {
                return false;
            }
            strCheck = arrData[0].Split(strSubSplit)[0];
            if (strCheck == strHeaderCheck)
            {
                return false;
            }
            if (strCheck == strSubTatolCheck)
            {
                return false;
            }
            if (string.IsNullOrEmpty(strCheck.Trim()))
            {
                return false;
            }

            return true;
        }

        ///// <summary>
        ///// 金额合计用function
        ///// </summary>
        ///// <param name="arr"></param>
        ///// <param name="iStart"></param>
        ///// <param name="iEnd"></param>
        ///// <returns></returns>
        //private decimal amtGet(string[] arr, int iStart, int iEnd)
        //{
        //    decimal reAmt;
        //    reAmt = 0;
        //    if (iStart == 0 && iEnd == 0)
        //    {
        //        iStart = 9;
        //        iEnd = 22;
        //    }
        //    for (int i = iStart; i <= iEnd; i++)
        //    {
        //        if (!string.IsNullOrEmpty(arr[i]))
        //        {
        //            reAmt = reAmt + Convert.ToDecimal(arr[i]);
        //        }
        //    }

        //    return reAmt;
        //}

        #region site info get from Db
        /// <summary>
        /// site 取得
        /// </summary>
        /// <param name="strSite"></param>
        /// <param name="sites"></param>
        /// <returns></returns>
        private string siteGet(string strType)
        {
            IBaseDataService Service = SpringFactory.GetObjectImpl<IBaseDataService>("BaseDataService");
            List<SysTypeDetail> sites = Service.GetSysTypeDetail("015");
            if (sites == null)
            {
                return null;
            }
            else
            {
                return sites.Where(o => o.DetailName == strType).Select(o => o.DetailValue).SingleOrDefault();
            }

        }
        #endregion

        #region Customer Info Get
        /// <summary>
        /// CustomerInfo取得
        /// </summary>
        /// <param name="CustomerNum"></param>
        /// <param name="custs"></param>
        /// <param name="billGroupInfos"></param>
        /// <returns></returns>
        private bool TryGetCustomer(string customerNum, string customerName, List<Customer> allCustList, List<Customer> jitCustList, List<CustomerTeam> AllCustTeamList, out Customer cust, out CustomerTeam custTeam)
        {
            cust = (from o in allCustList
                    where o.CustomerNum == customerNum && o.Deal == CurrentDeal
                    select o).FirstOrDefault();
            custTeam = (from o in AllCustTeamList
                    where o.CustomerNum == customerNum && o.Deal == CurrentDeal
                    select o).FirstOrDefault();

            if (cust != null)
            {
                if (cust.CustomerName != customerName)
                {
                    cust.CustomerName = customerName;
                }
                return true;
            }
            else if ((cust = jitCustList.Find(c => c.CustomerNum == customerNum && c.Deal == CurrentDeal)) != null)
            {

                return true;
            }
            else
            {
                return false;
            }
        }
        #endregion

        #region 类型转换
        /// <summary>
        /// 类型转换
        /// </summary>
        /// <param name="strData"></param>
        /// <returns></returns>
        private Decimal dataConvertToDec(string strData)
        {
            decimal rtn;
            rtn = 0;
            bool isRegex = false;
            if (!string.IsNullOrEmpty(strData))
            {

                Regex reg = new Regex(@"([+-]?)(\d\.\d{1,})E([-]?)(\d+)", RegexOptions.IgnoreCase);
                MatchCollection mac = reg.Matches(strData);
                foreach (Match m in mac)
                {
                    isRegex = true;
                    rtn = Convert.ToDecimal(Convert.ToDouble(m.Groups[0].Value));
                }
                //return Convert.ToDecimal(strData);
                if (!isRegex)
                {
                    rtn = Convert.ToDecimal(strData);
                }
            }
            else
            {
                rtn = 0;
            }
            return rtn;
        }
        #endregion

        /// <summary>
        /// 数据导入
        /// </summary>
        private void dataInvoiceImport(StreamReader sr, string strSite, string strGuid)
        {
            string line;//数据行读取用
            string[] arrData;
            InvoiceAgingStaging invaging;
            invoiceAgingList = new List<InvoiceAgingStaging>();
            int intCount;

            intCount = 0;

            var customerList = GetCustomer().ToList();

            while ((line = sr.ReadLine()) != null)
            {
                arrData = line.Split(strSplit);
                if (arrData.Length != intInvoiceCols)
                {
                    continue;
                }
                intCount++;

                //if (!CustAgingExist(strSite, arrData[1], AppContext.Current.User.EID))
                //{
                //    strSubMessage += arrData[12].ToString() + strEnter;
                //    isEx = true;
                //    continue;
                //}
                try
                {
                    invaging = new InvoiceAgingStaging();
                    invaging.CustomerNum = arrData[1];
                    //CustomerInfo取得
                    invaging.CustomerName = arrData[2];
                    invaging.InvoiceNum = arrData[12];
                    //invaging.MstCustomer = arrData[19];
                    //MstCustomer:RBO name
                    invaging.MstCustomer = arrData[21];
                    invaging.PoNum = arrData[17];
                    invaging.SoNum = arrData[26];
                    invaging.Class = arrData[16];
                    invaging.Currency = arrData[23];
                    invaging.OrderBy = arrData[27];
                    invaging.InvoiceDate = dataConvertToDT(arrData[13]);
                    invaging.DueDate = dataConvertToDT(arrData[15]);
                    invaging.OriginalAmt = dataConvertToDec(arrData[22]);
                    invaging.BalanceAmt = dataConvertToDec(arrData[24]);
                    invaging.LegalEntity = strSite;
                    invaging.Deal = CurrentDeal; // need change
                    invaging.CreateDate = AppContext.Current.User.Now;
                    invaging.Operator = AppContext.Current.User.EID;
                    invaging.CreditTrem = getTrem(arrData[14]);
                    invaging.ImportId = strGuid;
                    invaging.Comments = arrData[19];
                    invaging.States = strInvStats;

                    //upload all the col
                    invaging.StatementDate = dataConvertToDT(arrData[0]);
                    invaging.CustomerAddress1 = arrData[3];
                    invaging.CustomerAddress2 = arrData[4];
                    invaging.CustomerAddress3 = arrData[5];
                    invaging.CustomerAddress4 = arrData[6];
                    invaging.CustomerCountry = arrData[7];
                    invaging.CustomerCountryDetail = arrData[8];
                    invaging.AttentionTo = arrData[9];
                    invaging.CollectorName = arrData[10];
                    invaging.CollectorContact = arrData[11];
                    invaging.DaysLateSys = dataConvertToInt(arrData[18]);
                    invaging.RboCode = arrData[20];
                    invaging.OutstandingAccumulatedInvoiceAmt = dataConvertToDec(arrData[25]);
                    invaging.CustomerBillToSite = arrData[28];


                    Customer cust = customerList.Find(c => c.CustomerNum == invaging.CustomerNum && c.Deal == invaging.Deal);
                    if (cust != null)
                    {
                        invaging.BillGroupCode = cust.BillGroupCode;
                        invaging.CustomerName = cust.CustomerName;
                        if (cust.ExcludeFlg == "1")
                        {
                            continue;
                        }
                    }
                    invoiceAgingList.Add(invaging);
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
            }

        }

        private string getTrem(string tremTxt)
        {
            tremTxt = tremTxt.ToUpper().Replace("NET", "");

            if (string.IsNullOrEmpty(tremTxt))
            {
                return "0";
            }

            return tremTxt;
        }

        ///// <summary>
        ///// 对象数据存在性check
        ///// </summary>
        ///// <param name="strSite"></param>
        ///// <param name="strCustomerNum"></param>
        ///// <param name="strOprator"></param>
        ///// <returns></returns>
        //private bool CustAgingExist(string strSite, string strCustomerNum, string strOprator)
        //{
        //    string strDeal = "Avery";
        //    var cust = listAgingStagingEx
        //            .Where(inv => inv.Deal == strDeal
        //                    && inv.CustomerNum == strCustomerNum
        //                    && inv.Operator == strOprator).FirstOrDefault();
        //    if (cust == null)
        //    {
        //        return false;
        //    }
        //    else
        //    {
        //        return true;
        //    }
        //}

        /// <summary>
        /// 类型转换
        /// </summary>
        /// <param name="strData"></param>
        /// <returns></returns>
        private DateTime dataConvertToDT(string strData)
        {
            DateTime dt = new DateTime();
            if (!string.IsNullOrEmpty(strData.Trim()))
            {
                return Convert.ToDateTime(strData);
            }

            return dt;
        }

        private int dataConvertToInt(string strData)
        {
            int rtn;
            rtn = 0;
            if (!string.IsNullOrEmpty(strData))
            {
                rtn = Convert.ToInt32(strData);
            }

            return rtn;
        }

        
        /// <summary>
        /// click submit button, move tempTbale data to RealTable
        /// </summary>
        /// <param name="submitStgs"></param>
        /// <returns></returns>
        public int SubmitInitialAging()
        {
            Helper.Log.Info("Start submit");

            #region SQL
            // Account process logic
            // customer aging staging to customer aging
            var customerAgingMergeSql = string.Format(@"
        MERGE INTO T_CUSTOMER_AGING as Target 
        USING T_CUSTOMER_AGING_STAGING as Source
        ON
	        Target.DEAL=Source.DEAL and Target.CUSTOMER_NUM = Source.CUSTOMER_NUM 
	        and Target.LEGAL_ENTITY = Source.LEGAL_ENTITY and Source.DEAL = '{2}'
        WHEN MATCHED THEN 
	        UPDATE SET Target.CUSTOMER_NAME	    = Source.CUSTOMER_NAME
				        ,Target.CUSTOMER_CLASS	    = Source.CUSTOMER_CLASS
				        ,Target.RISK_SCORE		    = Source.RISK_SCORE
				        ,Target.BILL_GROUP_CODE	    = Source.BILL_GROUP_CODE
				        ,Target.BILL_GROUP_NAME	    = Source.BILL_GROUP_NAME
				        ,Target.COUNTRY			    = Source.COUNTRY
				        ,Target.CREDIT_TREM		    = Source.CREDIT_TREM
				        ,Target.CREDIT_LIMIT		= Source.CREDIT_LIMIT
				        ,Target.COLLECTOR		    = Source.COLLECTOR
				        ,Target.COLLECTOR_SYS	    = Source.COLLECTOR_SYS
				        ,Target.SALES			    = Source.SALES
				        ,Target.TOTAL_AMT		    = Source.TOTAL_AMT
				        ,Target.CURRENT_AMT		    = Source.CURRENT_AMT
				        ,Target.DUE30_AMT		    = Source.DUE30_AMT
				        ,Target.DUE60_AMT		    = Source.DUE60_AMT
				        ,Target.DUE90_AMT		    = Source.DUE90_AMT
				        ,Target.DUE120_AMT		    = Source.DUE120_AMT
				        ,Target.DUE150_AMT		    = Source.DUE150_AMT
				        ,Target.DUE180_AMT		    = Source.DUE180_AMT
				        ,Target.DUE210_AMT		    = Source.DUE210_AMT
				        ,Target.DUE240_AMT		    = Source.DUE240_AMT
				        ,Target.DUE270_AMT		    = Source.DUE270_AMT
				        ,Target.DUE300_AMT		    = Source.DUE300_AMT
				        ,Target.DUE330_AMT		    = Source.DUE330_AMT
				        ,Target.DUE360_AMT		    = Source.DUE360_AMT
				        ,Target.DUEOVER360_AMT	    = Source.DUEOVER360_AMT
				        ,Target.IS_HOLD_FLG		    = Source.IS_HOLD_FLG
				        ,Target.IMPORT_ID		    = Source.IMPORT_ID
                        ,Target.DUEOVER_TOTAL_AMT   = Source.TOTAL_AMT - Source.CURRENT_AMT
                        ,Target.UPDATE_DATE         = CAST('{1}' as datetime)
                        ,Target.REMOVE_FLG          = '0'
                        ,Target.CURRENCY            =Source.CURRENCY
                        ,Target.COUNTRY_CODE        =Source.COUNTRY_CODE
                        ,Target.OUTSTANDING_AMT     =Source.OUTSTANDING_AMT
                        ,Target.CITY_OR_STATE       =Source.CITY_OR_STATE
                        ,Target.CONTACT_NAME        =Source.CONTACT_NAME
                        ,Target.CONTACT_PHONE       =Source.CONTACT_PHONE
                        ,Target.CUSTOMER_CREDIT_MEMO=Source.CUSTOMER_CREDIT_MEMO
                        ,Target.CUSTOMER_PAYMENTS   =Source.CUSTOMER_PAYMENTS
                        ,Target.CUSTOMER_RECEIPTS_AT_RISK=Source.CUSTOMER_RECEIPTS_AT_RISK
                        ,Target.CUSTOMER_CLAIMS     =Source.CUSTOMER_CLAIMS
                        ,Target.CUSTOMER_BALANCE    =Source.CUSTOMER_BALANCE
                        
                        --,Target.'OPERATOR'          = '{0}'
        WHEN not matched and Source.DEAL = '{2}' THEN
	        insert 
		        (DEAL,LEGAL_ENTITY,CUSTOMER_NUM,CUSTOMER_NAME,CUSTOMER_CLASS,RISK_SCORE,BILL_GROUP_CODE
		        ,BILL_GROUP_NAME,COUNTRY,CREDIT_TREM,CREDIT_LIMIT,COLLECTOR,COLLECTOR_SYS,SALES
		        ,TOTAL_AMT,CURRENT_AMT,DUE30_AMT,DUE60_AMT,DUE90_AMT,DUE120_AMT,DUE150_AMT
		        ,DUE180_AMT,DUE210_AMT,DUE240_AMT,DUE270_AMT,DUE300_AMT,DUE330_AMT,DUE360_AMT,DUEOVER360_AMT
                , DUEOVER_TOTAL_AMT,CREATE_DATE,UPDATE_DATE--,OPERATOR
                ,IS_HOLD_FLG,IMPORT_ID,REMOVE_FLG
                ,CURRENCY, COUNTRY_CODE, OUTSTANDING_AMT, CITY_OR_STATE
                , CONTACT_NAME, CONTACT_PHONE, CUSTOMER_CREDIT_MEMO, CUSTOMER_PAYMENTS
                , CUSTOMER_RECEIPTS_AT_RISK, CUSTOMER_CLAIMS, CUSTOMER_BALANCE)
	        values (Source.DEAL,Source.LEGAL_ENTITY,Source.CUSTOMER_NUM,Source.CUSTOMER_NAME,Source.CUSTOMER_CLASS,Source.RISK_SCORE,Source.BILL_GROUP_CODE
			        ,Source.BILL_GROUP_NAME,Source.COUNTRY,Source.CREDIT_TREM,Source.CREDIT_LIMIT,Source.COLLECTOR,Source.COLLECTOR_SYS,Source.SALES
			        ,Source.TOTAL_AMT,Source.CURRENT_AMT,Source.DUE30_AMT,Source.DUE60_AMT,Source.DUE90_AMT,Source.DUE120_AMT,Source.DUE150_AMT
			        ,Source.DUE180_AMT,Source.DUE210_AMT,Source.DUE240_AMT,Source.DUE270_AMT,Source.DUE300_AMT,Source.DUE330_AMT,Source.DUE360_AMT,Source.DUEOVER360_AMT
                    , Source.TOTAL_AMT - Source.CURRENT_AMT,CAST('{1}' as datetime),CAST('{1}' as datetime)--,'{0}'
                    ,Source.IS_HOLD_FLG,Source.IMPORT_ID,'0'
                    ,Source.CURRENCY, Source.COUNTRY_CODE, Source.OUTSTANDING_AMT, Source.CITY_OR_STATE
                    , Source.CONTACT_NAME, Source.CONTACT_PHONE, Source.CUSTOMER_CREDIT_MEMO, Source.CUSTOMER_PAYMENTS
                    , Source.CUSTOMER_RECEIPTS_AT_RISK, Source.CUSTOMER_CLAIMS, Source.CUSTOMER_BALANCE)
        WHEN not matched by Source and Target.DEAL = '{2}' AND Target.LEGAL_ENTITY in (SELECT DISTINCT LEGAL_ENTITY FROM T_CUSTOMER_AGING_STAGING) THEN
            UPDATE SET Target.REMOVE_FLG = '1'
                       ,Target.UPDATE_DATE         = CAST('{1}' as datetime) 
                       ;", AppContext.Current.User.EID, AppContext.Current.User.Now, CurrentDeal);

            var invoiceAgingMergeSql = string.Format(@"
        MERGE INTO T_INVOICE_AGING as Target 
        USING (SELECT * FROM T_INVOICE_AGING_STAGING 
				WHERE CLASS+INVOICE_NUM+CUSTOMER_NUM+DEAL+LEGAL_ENTITY 
					NOT IN (
						SELECT CLASS+INVOICE_NUM+CUSTOMER_NUM+DEAL+LEGAL_ENTITY 
						FROM T_INVOICE_AGING_STAGING 
						WHERE CLASS IN ('DM','PAYMENT')
						GROUP BY CLASS, INVOICE_NUM, CUSTOMER_NUM, DEAL, LEGAL_ENTITY HAVING COUNT(1) > 1)
			  ) as Source
        ON
	        Target.DEAL = Source.DEAL and Target.CUSTOMER_NUM = Source.CUSTOMER_NUM 
	        and Target.LEGAL_ENTITY = Source.LEGAL_ENTITY and Target.INVOICE_NUM = Source.INVOICE_NUM and Source.DEAL = '{2}'
        WHEN MATCHED THEN 
			UPDATE SET Target.INVOICE_TYPE		= Source.INVOICE_TYPE
			,Target.CUSTOMER_NAME	= Source.CUSTOMER_NAME
			,Target.CREDIT_TREM		= Source.CREDIT_TREM
			,Target.MST_CUSTOMER		= Source.MST_CUSTOMER
			,Target.PO_MUM			= Source.PO_MUM
			,Target.SO_NUM			= Source.SO_NUM
			,Target.CLASS			= Source.CLASS
			,Target.CURRENCY			= Source.CURRENCY
			,Target.ORDER_BY			= Source.ORDER_BY
			,Target.BILL_GROUP_CODE	= Source.BILL_GROUP_CODE
			,Target.INVOICE_DATE		= Source.INVOICE_DATE
			,Target.DUE_DATE			= Source.DUE_DATE
			,Target.ORIGINAL_AMT		= Source.ORIGINAL_AMT
			,Target.BALANCE_AMT		= Source.BALANCE_AMT
			--Target.REMARK			--= Source.REMARK
			,Target.IMPORT_ID		= Source.IMPORT_ID
			,Target.UPDATE_DATE		= CAST('{1}' as datetime)
            ,Target.MISS_ACCOUNT_FLG=Source.MISS_ACCOUNT_FLG
            ,Target.STATEMENT_DATE=Source.STATEMENT_DATE
            ,Target.CUSTOMER_ADDRESS_1=Source.CUSTOMER_ADDRESS_1
            ,Target.CUSTOMER_ADDRESS_2=Source.CUSTOMER_ADDRESS_2
            ,Target.CUSTOMER_ADDRESS_3=Source.CUSTOMER_ADDRESS_3
            ,Target.CUSTOMER_ADDRESS_4=Source.CUSTOMER_ADDRESS_4
            ,Target.CUSTOMER_COUNTRY=Source.CUSTOMER_COUNTRY
            ,Target.CUSTOMER_COUNTRY_DETAIL=Source.CUSTOMER_COUNTRY_DETAIL
            ,Target.ATTENTION_TO=Source.ATTENTION_TO
            ,Target.COLLECTOR_NAME=Source.COLLECTOR_NAME
            ,Target.COLLECTOR_CONTACT=Source.COLLECTOR_CONTACT
            ,Target.DAYS_LATE_SYS=Source.DAYS_LATE_SYS
            ,Target.RBO_CODE=Source.RBO_CODE
            ,Target.OUTSTANDING_ACCUMULATED_INVOICE_AMT=Source.OUTSTANDING_ACCUMULATED_INVOICE_AMT
            ,Target.CUSTOMER_BILL_TO_SITE=Source.CUSTOMER_BILL_TO_SITE
            ,Target.STATES = (CASE WHEN Target.STATES = '{4}' THEN '{5}' ELSE Target.STATES END)
            ,Target.TRACK_STATES = (CASE WHEN Target.STATES = '{4}' THEN '{3}' ELSE Target.TRACK_STATES END)
			--Target.COMMENTS		--= Source.COMMENTS
		WHEN NOT MATCHED and Source.DEAL = '{2}' THEN
			INSERT (
				DEAL,CUSTOMER_NUM,LEGAL_ENTITY,CUSTOMER_NAME,INVOICE_NUM, INVOICE_TYPE,CREDIT_TREM,MST_CUSTOMER,
				PO_MUM,SO_NUM,CLASS,CURRENCY,STATES,ORDER_BY,BILL_GROUP_CODE,
				INVOICE_DATE,DUE_DATE,ORIGINAL_AMT,BALANCE_AMT,REMARK,
				IMPORT_ID,CREATE_DATE,UPDATE_DATE,COMMENTS
                , MISS_ACCOUNT_FLG, STATEMENT_DATE, CUSTOMER_ADDRESS_1, CUSTOMER_ADDRESS_2
                , CUSTOMER_ADDRESS_3, CUSTOMER_ADDRESS_4, CUSTOMER_COUNTRY
                , CUSTOMER_COUNTRY_DETAIL, ATTENTION_TO, COLLECTOR_NAME
                , COLLECTOR_CONTACT, DAYS_LATE_SYS, RBO_CODE
                , OUTSTANDING_ACCUMULATED_INVOICE_AMT, CUSTOMER_BILL_TO_SITE
                , TRACK_STATES
			)
			VALUES (
				Source.DEAL,Source.CUSTOMER_NUM,Source.LEGAL_ENTITY,Source.CUSTOMER_NAME,Source.INVOICE_NUM, Source.INVOICE_TYPE,Source.CREDIT_TREM,Source.MST_CUSTOMER,
				Source.PO_MUM,Source.SO_NUM,Source.CLASS,Source.CURRENCY,Source.STATES,Source.ORDER_BY,Source.BILL_GROUP_CODE,
				Source.INVOICE_DATE,Source.DUE_DATE,Source.ORIGINAL_AMT,Source.BALANCE_AMT,Source.REMARK,
				Source.IMPORT_ID,CAST('{1}' as datetime),CAST('{1}' as datetime),Source.COMMENTS
                , Source.MISS_ACCOUNT_FLG, Source.STATEMENT_DATE, Source.CUSTOMER_ADDRESS_1, Source.CUSTOMER_ADDRESS_2
                , Source.CUSTOMER_ADDRESS_3, Source.CUSTOMER_ADDRESS_4, Source.CUSTOMER_COUNTRY
                , Source.CUSTOMER_COUNTRY_DETAIL, Source.ATTENTION_TO, Source.COLLECTOR_NAME
                , Source.COLLECTOR_CONTACT, Source.DAYS_LATE_SYS, Source.RBO_CODE
                , Source.OUTSTANDING_ACCUMULATED_INVOICE_AMT, Source.CUSTOMER_BILL_TO_SITE
                , '{3}'
			);", AppContext.Current.User.EID, AppContext.Current.User.Now, CurrentDeal
               , Helper.EnumToCode<TrackStatus>(TrackStatus.Open)
               , Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.Closed)
               , Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.Open));

            // for Payment and DM, there are duplicate records with same invoice number. They will be treat specially because of 'Merge' clause will fail on them.
            var specialPaymentAdditionSql = string.Format(@"
        INSERT INTO T_INVOICE_AGING (
			DEAL,CUSTOMER_NUM,LEGAL_ENTITY,CUSTOMER_NAME,INVOICE_NUM, INVOICE_TYPE,CREDIT_TREM,MST_CUSTOMER,
			PO_MUM,SO_NUM,CLASS,CURRENCY,STATES,ORDER_BY,BILL_GROUP_CODE,
			INVOICE_DATE,DUE_DATE,ORIGINAL_AMT,BALANCE_AMT,REMARK,
			IMPORT_ID,CREATE_DATE,UPDATE_DATE,COMMENTS
            , MISS_ACCOUNT_FLG, STATEMENT_DATE, CUSTOMER_ADDRESS_1, CUSTOMER_ADDRESS_2
            , CUSTOMER_ADDRESS_3, CUSTOMER_ADDRESS_4, CUSTOMER_COUNTRY
            , CUSTOMER_COUNTRY_DETAIL, ATTENTION_TO, COLLECTOR_NAME
            , COLLECTOR_CONTACT, DAYS_LATE_SYS, RBO_CODE
            , OUTSTANDING_ACCUMULATED_INVOICE_AMT, CUSTOMER_BILL_TO_SITE
		) 
		SELECT Source.DEAL,Source.CUSTOMER_NUM,Source.LEGAL_ENTITY,Source.CUSTOMER_NAME,Source.INVOICE_NUM, Source.INVOICE_TYPE,Source.CREDIT_TREM,Source.MST_CUSTOMER,
			Source.PO_MUM,Source.SO_NUM,Source.CLASS,Source.CURRENCY,Source.STATES,Source.ORDER_BY,Source.BILL_GROUP_CODE,
			Source.INVOICE_DATE,Source.DUE_DATE,Source.ORIGINAL_AMT,Source.BALANCE_AMT,Source.REMARK,
			Source.IMPORT_ID,CAST('{0}' as datetime),CAST('{0}' as datetime),
			Source.COMMENTS
            , Source.MISS_ACCOUNT_FLG, Source.STATEMENT_DATE, Source.CUSTOMER_ADDRESS_1, Source.CUSTOMER_ADDRESS_2
            , Source.CUSTOMER_ADDRESS_3, Source.CUSTOMER_ADDRESS_4, Source.CUSTOMER_COUNTRY
            , Source.CUSTOMER_COUNTRY_DETAIL, Source.ATTENTION_TO, Source.COLLECTOR_NAME
            , Source.COLLECTOR_CONTACT, Source.DAYS_LATE_SYS, Source.RBO_CODE
            , Source.OUTSTANDING_ACCUMULATED_INVOICE_AMT, Source.CUSTOMER_BILL_TO_SITE
        FROM T_INVOICE_AGING_STAGING Source
		WHERE CLASS+INVOICE_NUM+CUSTOMER_NUM+DEAL+LEGAL_ENTITY 
			IN (
				SELECT CLASS+INVOICE_NUM+CUSTOMER_NUM+DEAL+LEGAL_ENTITY 
				FROM T_INVOICE_AGING_STAGING 
				WHERE CLASS IN ('DM','PAYMENT')
				GROUP BY CLASS, INVOICE_NUM, CUSTOMER_NUM, DEAL, LEGAL_ENTITY HAVING COUNT(1) > 1)", AppContext.Current.User.Now);

            var invoiceAgingClosingSql = string.Format(@"
        UPDATE T_INVOICE_AGING
        SET UPDATE_DATE = CAST('{1}' as datetime), REMARK = 'Missing invoice during import. Set to paied status by SYSTEM'
            ,STATES = '{3}',TRACK_STATES = '{9}'
        WHERE 
	        NOT EXISTS (SELECT * FROM T_INVOICE_AGING_STAGING Staging WHERE T_INVOICE_AGING.DEAL = Staging.DEAL 
				        and T_INVOICE_AGING.CUSTOMER_NUM = Staging.CUSTOMER_NUM 
				        and T_INVOICE_AGING.LEGAL_ENTITY = Staging.LEGAL_ENTITY and T_INVOICE_AGING.INVOICE_NUM = Staging.INVOICE_NUM)
            and EXISTS (SELECT * FROM T_CUSTOMER_AGING_STAGING Staging WHERE T_INVOICE_AGING.DEAL = Staging.DEAL 
			--	        and T_INVOICE_AGING.CUSTOMER_NUM = Staging.CUSTOMER_NUM 
				        and T_INVOICE_AGING.LEGAL_ENTITY = Staging.LEGAL_ENTITY)
            and T_INVOICE_AGING.DEAL = '{2}'
            --and STATES in ('{4}','{5}','{6}','{7}','{8}')
            and STATES <> '{3}';
            ", AppContext.Current.User.EID, CurrentTime, CurrentDeal, Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.Closed)
             , strInvStats
             , Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.PTP)
             , Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.Paid)
             , Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.PartialPay)
             , Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.Dispute)
             , Helper.EnumToCode<TrackStatus>(TrackStatus.Close));

            var invoiceLog = string.Format(@"
                INSERT INTO T_INVOICE_LOG
                (DEAL, CUSTOMER_NUM, INVOICE_ID,
                LOG_DATE, LOG_PERSON, LOG_ACTION, 
                LOG_TYPE, OLD_STATUS, NEW_STATUS, 
                OLD_TRACK,NEW_TRACK,
                CONTACT_PERSON, PROOF_ID, DISCRIPTION)
                SELECT '{0}',ISNULL(staing.CUSTOMER_NUM,aging.CUSTOMER_NUM),ISNULL(staing.INVOICE_NUM,aging.INVOICE_NUM),
                        CAST('{1}' as datetime),'{2}','{3}',
                        '0',CASE WHEN aging.ID is null then '{4}' else aging.STATES end,CASE WHEN staing.ID is null then '{5}' else '{4}' end,
                        CASE WHEN aging.ID is null then '{10}' else aging.TRACK_STATES end,
                        CASE WHEN staing.ID is null then '{11}' else '{10}' end,
                        '{2}',null,null
                FROM T_INVOICE_AGING_STAGING staing
                FULL OUTER JOIN T_INVOICE_AGING    aging
                ON staing.DEAL = aging.DEAL
                AND staing.CUSTOMER_NUM = aging.CUSTOMER_NUM
                AND staing.LEGAL_ENTITY = aging.LEGAL_ENTITY
                AND staing.INVOICE_NUM = aging.INVOICE_NUM
                WHERE aging.ID is null
                OR
                    (
                        staing.ID is null
                    AND
                        --aging.STATES in ('{4}','{6}','{7}','{8}','{9}')
                        aging.STATES <> '{5}'
                    AND
                        EXISTS (SELECT * FROM T_CUSTOMER_AGING_STAGING Staging WHERE aging.DEAL = Staging.DEAL 
				    --    and aging.CUSTOMER_NUM = Staging.CUSTOMER_NUM 
				        and aging.LEGAL_ENTITY = Staging.LEGAL_ENTITY)
                    );
                ", CurrentDeal, AppContext.Current.User.Now
                 , AppContext.Current.User.EID
                 , "Upload", strInvStats
                 , Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.Closed)
                 , Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.PTP)
                 , Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.Paid)
                 , Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.PartialPay)
                 , Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.Dispute)
                 , Helper.EnumToCode<TrackStatus>(TrackStatus.Open)
                 , Helper.EnumToCode<TrackStatus>(TrackStatus.Close));

            var invoiceLog2 = string.Format(@"
                INSERT INTO T_INVOICE_LOG
                (DEAL, CUSTOMER_NUM, INVOICE_ID,
                LOG_DATE, LOG_PERSON, LOG_ACTION, 
                LOG_TYPE, OLD_STATUS, NEW_STATUS, 
                OLD_TRACK,NEW_TRACK,
                CONTACT_PERSON, PROOF_ID, DISCRIPTION)
                SELECT '{0}',aging.CUSTOMER_NUM,aging.INVOICE_NUM,
                        CAST('{1}' as datetime),'{2}','{3}',
                        '0',aging.STATES,'{4}',
                        aging.TRACK_STATES,'{6}',
                        '{2}',null,null
                FROM T_INVOICE_AGING_STAGING staing
                INNER JOIN T_INVOICE_AGING    aging
                ON staing.DEAL = aging.DEAL
                AND staing.CUSTOMER_NUM = aging.CUSTOMER_NUM
                AND staing.LEGAL_ENTITY = aging.LEGAL_ENTITY
                AND staing.INVOICE_NUM = aging.INVOICE_NUM
                WHERE aging.STATES = '{5}';
                ", CurrentDeal, AppContext.Current.User.Now
                 , AppContext.Current.User.EID
                 , "Upload", strInvStats
                 , Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.Closed)
                 , Helper.EnumToCode<TrackStatus>(TrackStatus.Open));
            #endregion

            #region Removed checking
            // Check if there are account without staging invoice.
            //if (stgInvoices == null || stgInvoices.Count == 0)
            //{
            //    throw new OTCServiceException("There are no invoice for the selected accounts!");
            //}

            //List<string> accountMissingInvoice = new List<string>();
            //invoicKeys.ForEach(key =>
            //{
            //    if (!stgInvoices.Exists(inv => inv.Deal + inv.LegalEntity + inv.CustomerNum + inv.Operator == key))
            //    {
            //        accountMissingInvoice.Add(key);
            //    }
            //});

            //if (accountMissingInvoice.Count > 0)
            //{
            //    throw new OTCServiceException("There are accounts don't have invoice information, Please make sure they are uploaded!");
            //}
            #endregion
            
            try
            {
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required,new TimeSpan(0, 30, 0)))
                {
                    Helper.Log.Info("Transaction scope created");

                    Helper.Log.Info("File history update started.");
                    //Get custAgStags info
                    var custAgStags = CommonRep.GetQueryable<CustomerAgingStaging>().Where(o => o.Deal == CurrentDeal).ToList();

                    //use slelected id to search temptable not find result(this reco other user commited)
                    if (custAgStags.Count == 0) { return 1; }

                    List<FileUploadHistory> listImport = (from cust in custAgStags
                                                          group cust by cust.ImportId
                                                              into custg
                                                              select new FileUploadHistory
                                                              {
                                                                  ImportId = custg.Key
                                                              }).ToList();

                    FileService fileService = SpringFactory.GetObjectImpl<FileService>("FileService");
                    List<FileUploadHistory> file = fileService.GetSucDataByImportId(listImport);
                    fileService.commitHisUp(file);
                    Helper.Log.Info("File history update complete.");
                    CommonRep.Commit();

                    Helper.Log.Info("Merge(insert/update) the account level aging data to database.");
                    CommonRep.GetDBContext().Database.ExecuteSqlCommand(customerAgingMergeSql);

                    Helper.Log.Info("insert into T_INVOICE_LOG whitch invoice's STATES has changed.");
                    CommonRep.GetDBContext().Database.ExecuteSqlCommand(invoiceLog);

                    CommonRep.GetDBContext().Database.ExecuteSqlCommand(invoiceLog2);

                    Helper.Log.Info("Merge(insert/update) the invoice level aging data to database.");
                    CommonRep.GetDBContext().Database.ExecuteSqlCommand(invoiceAgingMergeSql);

                    Helper.Log.Info("Merge(insert/update) the invoice level aging data to database for special Payment.");
                    CommonRep.GetDBContext().Database.ExecuteSqlCommand(specialPaymentAdditionSql);
                    
                    Helper.Log.Info("Apply closing logic to the missing invoice level aging data.");
                    CommonRep.GetDBContext().Database.ExecuteSqlCommand(invoiceAgingClosingSql);

                    Helper.Log.Info("Start to delete the staging data.");
                    CommonRep.GetDBContext().Database.ExecuteSqlCommand("delete from T_CUSTOMER_AGING_STAGING where DEAL = '" + CurrentDeal + "';");
                    CommonRep.GetDBContext().Database.ExecuteSqlCommand("delete from T_INVOICE_AGING_STAGING WHERE DEAL = '" + CurrentDeal + "';");
                    
                    Helper.Log.Info("Completed invoice level aging process.");

                    // finaly commit all 
                    scope.Complete();
                }
            }
            catch (Exception ex)
            {
                Helper.Log.Error("Error happended while save submit aging.", ex);
                throw;
            }
            return 0;
        }

        public void SubmitOneYearSales()
        {
            #region delete
            ////0.value his insert
            //List<CustomerChangeHis> changhises = new List<CustomerChangeHis>();
            //CustomerChangeHis changhis = new CustomerChangeHis();

            ////1.OneYearSales info update from staing to master
            //List<GroupStaing> grps = new List<GroupStaing>();
            //grps = GetGroupStaing();

            //CustomerGroupCfgService service = SpringFactory.GetObjectImpl<CustomerGroupCfgService>("CustomerGroupCfgService");
            //List<CustomerGroupCfg> grpcfgs = new List<CustomerGroupCfg>();
            //grpcfgs = service.GetAllGroups().ToList();
            //CustomerGroupCfg grpcfg = new CustomerGroupCfg();
            //List<CustomerGroupCfg> grpcfgAdds = new List<CustomerGroupCfg>();
            //CustomerGroupCfg grpcfgAdd = new CustomerGroupCfg();

            //RiskExceptionListService riskservice = SpringFactory.GetObjectImpl<RiskExceptionListService>("RiskExceptionListService");
            //List<RiskExceptionList> listExcept = new List<RiskExceptionList>();
            //listExcept = riskservice.getAllRiskExceptionByValue("1");

            //List<Customer> custs = new List<Customer>();
            //custs = GetCustomer();

            //foreach (Customer cust in custs)
            //{
            //    cust.VClass = "";
            //    cust.OneYearSales = 0;
            //}
            #endregion

            List<CustomerGroupCfgStaging> grps = new List<CustomerGroupCfgStaging>();
            grps = GetGroupStaing();

            if (grps.Count == 0)
            {
                throw new OTCServiceException("No data needs submit!");
            }

            //2.uploadinfo update
            #region SQL

            var GroupCfgUpdateList = string.Format(@"
                UPDATE
	                cfg
                SET 
	                BILL_GROUP_NAME = ISNULL(frp.BILL_GROUP_NAME,cfg.BILL_GROUP_NAME)
                FROM 
	                T_CUSTOMER_GROUP_CFG cfg
                LEFT JOIN
	                T_CUSTOMER_GROUP_CFG_STAGING frp
                ON
	                cfg.DEAL = frp.DEAL
                AND
	                cfg.BILL_GROUP_CODE = frp.BILL_GROUP_CODE
                WHERE 
                    cfg.DEAL = '{0}';
                ", CurrentDeal);

            var GroupCfgInsertList = string.Format(@"
                INSERT INTO T_CUSTOMER_GROUP_CFG
                (
	                DEAL, BILL_GROUP_CODE, BILL_GROUP_NAME
                )
                SELECT 
	                DEAL, BILL_GROUP_CODE, BILL_GROUP_NAME
                FROM 
	                T_CUSTOMER_GROUP_CFG_STAGING frp
                WHERE NOT EXISTS
                (
	                SELECT * FROM 
		                T_CUSTOMER_GROUP_CFG cfg
	                WHERE 
		                cfg.DEAL = frp.DEAL
	                AND
		                cfg.BILL_GROUP_CODE = frp.BILL_GROUP_CODE
                )
                AND 
                    frp.DEAL = '{0}';
                ", CurrentDeal);

            //var thresholdConfig = BDService.GetSysConfigByCode("001");
            //decimal dec = decimal.Parse(thresholdConfig.CfgValue);

            PeroidService perService = SpringFactory.GetObjectImpl<PeroidService>("PeroidService");
            int? perId = perService.getIdOfcurrentPeroid();

            /*var hisInsertChangeList = string.Format(@"
                INSERT INTO
                T_CUSTOMER_CHANGE_HIS
                (
                    PERIOD_ID, DEAL, CUSTOMER_NUM, CREATE_DATE, VALUE, VALUE_LEVEL, VR_TYPE, VR_DESC
                )
                SELECT
                    {4},
	                cust.DEAL,cust.CUSTOMER_NUM,CAST('{5}' as datetime),
                    frp.ONE_YEAR_SALES,
                    (CASE WHEN list.ID is null then 
                    (CASE WHEN frp.ONE_YEAR_SALES > {1} THEN '{2}' ELSE '{3}' END)
					ELSE list.EX_VALUE END) AS VALUE_LEVEL,
                    '1','VALUE IS :' + (CASE WHEN list.ID is null then 
                    (CASE WHEN frp.ONE_YEAR_SALES > {1} THEN '{2}' ELSE '{3}' END)
					ELSE list.EX_VALUE END)
                FROM
	                T_CUSTOMER cust
                LEFT JOIN
	                T_CUSTOMER_PRIORITIZATION_EXCEPTION_LIST list
                ON
	                cust.DEAL = list.DEAL
                AND 
	                cust.CUSTOMER_NUM = list.CUSTOMER_NUM
                AND
	                list.EX_TYPE = '1'
                AND
                    CAST('{5}' as datetime) BETWEEN list.EFFECT_DATE AND list.[EXPIRY_DATE]
                LEFT JOIN
	                T_CUSTOMER_GROUP_CFG_STAGING frp
                ON
	                cust.DEAL = frp.DEAL
                AND
	                cust.BILL_GROUP_CODE = frp.BILL_GROUP_CODE
                WHERE 
                    cust.DEAL = '{0}'
                AND
                    (list.ID is not null
                    OR
                    frp.ID is not null);
                ", CurrentDeal
                 , dec
                 ,CustomerClass.HV.ToString()
                 ,CustomerClass.LV.ToString()
                 , perId
                 ,CurrentTime);*/

            var hisInsertList = string.Format(@"
                INSERT INTO 
                T_CUSTOMER_GROUP_CFG_HISTORY
                (
	                PERIOD_ID, DEAL, BILL_GROUP_CODE, BILL_GROUP_NAME, ONEYEAR_SALES, IMPORT_ID, UPLOAD_TIME
                )
                SELECT 
	                {1}, DEAL, BILL_GROUP_CODE, BILL_GROUP_NAME, ONE_YEAR_SALES, IMPORT_ID, UPLOAD_TIME
                FROM
	                T_CUSTOMER_GROUP_CFG_STAGING
                WHERE 
                    DEAL = '{0}';
                ", CurrentDeal, perId);

            #endregion

            #region delete
            //foreach (GroupStaing grp in grps)
            //{
            //    grpcfg = new CustomerGroupCfg();
            //    grpcfg = grpcfgs.FindAll(o => o.Deal == grp.Deal &&
            //                o.BillGroupCode == grp.BillGroupCode).FirstOrDefault();
            //    if (grpcfg == null)
            //    {
            //        grpcfgAdd = new CustomerGroupCfg();
            //        grpcfgAdd.BillGroupCode = grp.BillGroupCode;
            //        grpcfgAdd.BillGroupName = grp.BillGroupName;
            //        grpcfgAdd.OneYearSales = grp.OneYearSales;
            //        grpcfgAdd.Deal = CurrentDeal;
            //        grpcfgAdds.Add(grpcfgAdd);
            //    }
            //    else
            //    {
            //        grpcfg.BillGroupName = grp.BillGroupName;
            //        grpcfg.OneYearSales = grp.OneYearSales;
            //        foreach (Customer cust in grpcfg.Custmer)
            //        {
            //            cust.VClass = grp.VClass;
            //            cust.OneYearSales = grp.OneYearSales;

            //            changhis = new CustomerChangeHis();
            //            changhis.CreateDate = DateTime.Now;
            //            changhis.Deal = grp.Deal;
            //            changhis.CustomerNum = cust.CustomerNum;
            //            changhis.VrType = "1";
            //            changhis.VrDesc = "Value is:" + grp.VClass;
            //            changhises.Add(changhis);
            //        }
            //    }
            //}
            //Customer ct = new Customer();
            //foreach (RiskExceptionList risk in listExcept)
            //{
            //    ct = custs.FindAll(o => o.CustomerNum == risk.CustomerNum &&
            //                        o.Deal == risk.Deal).FirstOrDefault();

            //    if (ct != null)
            //    {
            //        ct.VClass = risk.ExValue;
            //        //ct.OneYearSales = risk.OneYearSales;
            //    }
            //}
            #endregion
            try
            {
                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 30, 0)))
                {
                    Helper.Log.Info("Transaction scope created");

                    Helper.Log.Info("Completed one-year-sales's submit.");

                    Helper.Log.Info("update T_CUSTOMER_GROUP_CFG whith is exists.");
                    CommonRep.GetDBContext().Database.ExecuteSqlCommand(GroupCfgUpdateList);

                    Helper.Log.Info("insert into T_CUSTOMER_GROUP_CFG whith is not exists");
                    CommonRep.GetDBContext().Database.ExecuteSqlCommand(GroupCfgInsertList);

                    //Helper.Log.Info("insert into change history.");
                    //CommonRep.GetDBContext().Database.ExecuteSqlCommand(hisInsertChangeList);

                    Helper.Log.Info("insert into T_CUSTOMER_GROUP_CFG_HISTORY.");
                    CommonRep.GetDBContext().Database.ExecuteSqlCommand(hisInsertList);

                    CommonRep.GetDBContext().Database.ExecuteSqlCommand("delete from T_CUSTOMER_GROUP_CFG_STAGING where DEAL = '" + CurrentDeal + "'");

                    Helper.Log.Info("Completed one-year-sales's submit.");

                    // finaly commit all 
                    scope.Complete();
                }
                //CommonRep.RemoveRange(grps);
                //CommonRep.AddRange(grpcfgAdds);
                //CommonRep.AddRange(changhises);
                //CommonRep.Commit();

                //commit info write
                string strCode;
                FileUploadHistory file;
                FileService fileService = SpringFactory.GetObjectImpl<FileService>("FileService");
                strCode = Helper.EnumToCode<FileType>(FileType.OneYearSales);
                file = fileService.GetNewestSucData(strCode);
                fileService.commitHisUp(file,perId);
            }
            catch (Exception ex)
            {
                throw (ex);
                throw new OTCServiceException("Submit Failed!");
            }

        }
        public void ArBalanceAmtPeroidSet()
        {
            var updateSql = string.Format(@"
                UPDATE T_CUSTOMER_AGING
                SET	
	                AR_BALANCE_PERIOD = CASE WHEN REMOVE_FLG = '0' THEN TOTAL_AMT ELSE 0 END
                WHERE
	                DEAL = '{0}'
                ", CurrentDeal);

            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 30, 0)))
            {
                Helper.Log.Info("ArBalanceAmtPeroid UPDATE Start");

                CommonRep.GetDBContext().Database.ExecuteSqlCommand(updateSql);

                Helper.Log.Info("ArBalanceAmtPeroid UPDATE End");
                // finaly commit all 
                scope.Complete();
            }
        }
        public void GetValue()
        {
            var thresholdConfig = BDService.GetSysConfigByCode("001");
            decimal dec = decimal.Parse(thresholdConfig.CfgValue);

            FileService fileService = SpringFactory.GetObjectImpl<FileService>("FileService");
            string strImportId = fileService.getNeweastImportIdOfOneYear();

            var hisInsertChangeList = string.Format(@"
                INSERT INTO
                T_CUSTOMER_CHANGE_HIS
                (
                    PERIOD_ID, DEAL, CUSTOMER_NUM, CREATE_DATE, VALUE, VALUE_LEVEL, VR_TYPE, VR_DESC
                )
                SELECT
                    frp.PERIOD_ID,
	                cust.DEAL,cust.CUSTOMER_NUM,CAST('{4}' as datetime),
                    frp.ONE_YEAR_SALES,
                    (CASE WHEN list.ID is null then 
                    (CASE WHEN frp.ONE_YEAR_SALES > {1} THEN '{2}' ELSE '{3}' END)
					ELSE list.EX_VALUE END) AS VALUE_LEVEL,
                    '1','VALUE IS :' + (CASE WHEN list.ID is null then 
                    (CASE WHEN frp.ONE_YEAR_SALES > {1} THEN '{2}' ELSE '{3}' END)
					ELSE list.EX_VALUE END)
                FROM
	                T_CUSTOMER cust
                LEFT JOIN
	                T_CUSTOMER_PRIORITIZATION_EXCEPTION_LIST list
                ON
	                cust.DEAL = list.DEAL
                AND 
	                cust.CUSTOMER_NUM = list.CUSTOMER_NUM
                AND
	                list.EX_TYPE = '1'
                AND
                    CAST('{4}' as datetime) BETWEEN list.EFFECT_DATE AND list.[EXPIRY_DATE]
                LEFT JOIN
	                (SELECT ID,DEAL,BILL_GROUP_CODE,ONEYEAR_SALES AS ONE_YEAR_SALES,PERIOD_ID FROM
	                    T_CUSTOMER_GROUP_CFG_HISTORY
                     WHERE
	                    IMPORT_ID = '{5}') frp
                ON
	                cust.DEAL = frp.DEAL
                AND
	                cust.BILL_GROUP_CODE = frp.BILL_GROUP_CODE
                WHERE 
                    cust.DEAL = '{0}'
                AND
                    (list.ID is not null
                    OR
                    frp.ID is not null);
                ", CurrentDeal
                 , dec
                 , CustomerClass.HV.ToString()
                 , CustomerClass.LV.ToString()
                 , CurrentTime
                 , strImportId);


            using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 30, 0)))
            {
                Helper.Log.Info("Transaction(Value) scope created");

                Helper.Log.Info("insert into change history.");
                CommonRep.GetDBContext().Database.ExecuteSqlCommand(hisInsertChangeList);

                Helper.Log.Info("Transaction(Value) scope completed");
                // finaly commit all 
                scope.Complete();
            }
        }
        public IQueryable<CustomerAging> GetCustomerAgingByCollector(string eID)
        {
            var retCusAging = CommonRep.GetQueryable<CustomerAging>().Where(cusAging => cusAging.Collector == eID);
            return retCusAging;
        }

        #region Un-used
        public void DeleteCustomerAging(List<int> custIds)
        {
            AssertUtils.ArgumentHasElements(custIds, "Custome Ids");

            var res = (from c in CommonRep.GetQueryable<CustomerAgingStaging>()
                       where custIds.Contains(c.Id)
                       select c).ToList();
            if (res.Count > 0)
            {
                var keys = res.Select(c => c.Deal + c.CustomerNum + c.LegalEntity + c.Operator).ToList();

                List<InvoiceAgingStaging> invoice = CommonRep.GetQueryable<InvoiceAgingStaging>().Where(m => keys.Contains(m.Deal + m.CustomerNum + m.LegalEntity + m.Operator)).ToList();

                if (invoice.Count > 0)
                {
                    //delete invoice informations
                    CommonRep.RemoveRange(invoice as IEnumerable<InvoiceAgingStaging>);
                }

                CommonRep.RemoveRange(res as IEnumerable<CustomerAgingStaging>);

                CommonRep.Commit();
            }
        }
        #endregion

        public List<CustomerAgingStaging> getAmtNotEqualAging(out List<InvoiceAgingStaging> invlist)
        {
            //listAgingStaging invoiceAgingList
            List<CustomerAgingStaging> rtns;

            invlist = (from inv in invoiceAgingList//GetCustomerAgingStaging()
                                                       group inv by new { inv.LegalEntity, inv.CustomerNum } into invg
                                                       select new InvoiceAgingStaging
                                                       {
                                                           LegalEntity = invg.Key.LegalEntity,
                                                           CustomerNum = invg.Key.CustomerNum,
                                                           BalanceAmt = invg.Sum(o => o.BalanceAmt)
                                                       }).ToList();

            rtns = (from acc in listAgingStaging
                    join inv in invlist
                    on new { acc.LegalEntity, acc.CustomerNum } equals new { inv.LegalEntity, inv.CustomerNum }
                    where acc.TotalAmt != inv.BalanceAmt
                    select acc).ToList();

            return rtns;
        }

        /// <summary>
        /// Consolidate_Report
        /// </summary>
        /// <param name="strimportId"></param>
        public void createAgingReport()
        {

            Helper.Log.Info("Start create aging report!");

            string strTmpPath;
//            string strTmpInvPath;
            string strReportPath;
//            string strReportInvPath;
            string strReportName;
//            string strReportInvName;
            string strimportId;
            string strportPath;
            string strimportUploadId;
            List<CustomerGroupCfgHistory> custGrpHis = new List<CustomerGroupCfgHistory>();
            strimportId = System.Guid.NewGuid().ToString("N");
            FileService fileService = SpringFactory.GetObjectImpl<FileService>("FileService");

            strTmpPath = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings[strTempPathKey].ToString());
//            strTmpInvPath = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings[strTempPathInvKey].ToString()); 
            strportPath = ConfigurationManager.AppSettings[strArchivePathKey].ToString();
            strReportPath = HttpContext.Current.Server.MapPath(strportPath).ToString();
            if (Directory.Exists(strReportPath) == false)
            {
                Directory.CreateDirectory(strReportPath);
            }
//            strReportInvName = "Invoice Level Aging" + AppContext.Current.User.EID.ToString() + "_" + AppContext.Current.User.Now.ToString("yyyyMMddHHmmss") + ".xlsx";
//            strReportInvPath = strReportPath + strReportInvName;

            #region aging report
            strReportName = "Consolidate_Report_" + AppContext.Current.User.EID.ToString() + "_" + AppContext.Current.User.Now.ToString("yyyyMMddHHmmss") + ".xlsx";

            strReportPath  += strReportName;

            List<AgingReport> custAgingHis;
            custAgingHis = new List<AgingReport>();

            strimportUploadId = fileService.getNeweastImportIdOfOneYear();
            if (strimportUploadId != null)
            {
                custGrpHis = GetAllCustomerGroupHis().Where(o => o.ImportId == strimportUploadId && o.Deal == CurrentDeal).Select(o => o).ToList();
            }

            List<Customer> custlist = GetCustomer().Where(o => o.ExcludeFlg != "1").ToList();

            try
            {
                custAgingHis
                        = (from his in GetCurrentPerCustAging()
                           join team in GetCustomerTeam().ToList()
                           on new { his.Deal, his.CustomerNum }
                              equals new { team.Deal, team.CustomerNum }
                           join cfg in custGrpHis
                            on new { his.Deal, his.BillGroupCode } equals
                                  new { cfg.Deal, cfg.BillGroupCode }
                          into dft
                           from dftcfg in dft.DefaultIfEmpty()
                           select new AgingReport
                           {
                               Deal = his.Deal,
                               LegalEntity = his.LegalEntity,
                               Team = team.TeamName,
                               CustomerNum = his.CustomerNum,
                               CustomerName = his.CustomerName,
                               BillGroupCode = team.BillGroupCode,
                               BillGroupName = team.BillGroupName,
                               Country = his.Country,
                               CreditTrem = his.CreditTrem,
                               CreditLimit = his.CreditLimit,
                               Collector = team.CollectorName,
                               Collectorsys = his.CollectorSys,
                               Sales = team.Sales,
                               TotalAmt = his.TotalAmt,
                               CurrentAmt = his.CurrentAmt,
                               Due30Amt = his.Due30Amt,
                               Due60Amt = his.Due60Amt,
                               Due90Amt = his.Due90Amt,
                               Due180Amt = his.Due120Amt + his.Due150Amt + his.Due180Amt,
                               Due360Amt = his.Due210Amt + his.Due240Amt + his.Due270Amt +
                                           his.Due300Amt + his.Due330Amt + his.Due360Amt,
                               DueOver360Amt = his.DueOver360Amt,
                               DueOver60Amt = 0,
                               DueOver90Amt = 0,
                               DueOver180Amt = 0,
                               AdjustedDueOver60Amt = 0,
                               AdjustedDueOver90Amt = 0,
                               //AdjustedDueOver60Amt = (his.Due90Amt >0 ? his.Due90Amt:0)
                               //                     + (his.Due120Amt > 0 ? his.Due120Amt : 0)
                               //                     + (his.Due150Amt > 0 ? his.Due150Amt : 0)
                               //                     +(his.Due180Amt > 0 ? his.Due180Amt : 0)
                               //                     +(his.Due210Amt > 0 ? his.Due210Amt : 0)
                               //                     +(his.Due240Amt > 0 ? his.Due240Amt : 0)
                               //                     +(his.Due270Amt > 0 ? his.Due270Amt : 0)
                               //                     +(his.Due300Amt > 0 ? his.Due300Amt : 0)
                               //                     +(his.Due330Amt > 0 ? his.Due330Amt : 0)
                               //                     +(his.Due360Amt > 0 ? his.Due360Amt : 0)
                               //                     +(his.DueOver360Amt > 0 ? his.DueOver360Amt : 0),
                               //AdjustedDueOver90Amt = (his.Due120Amt > 0 ? his.Due120Amt : 0)
                               //                     + (his.Due150Amt > 0 ? his.Due150Amt : 0)
                               //                     + (his.Due180Amt > 0 ? his.Due180Amt : 0)
                               //                     + (his.Due210Amt > 0 ? his.Due210Amt : 0)
                               //                     + (his.Due240Amt > 0 ? his.Due240Amt : 0)
                               //                     + (his.Due270Amt > 0 ? his.Due270Amt : 0)
                               //                     + (his.Due300Amt > 0 ? his.Due300Amt : 0)
                               //                     + (his.Due330Amt > 0 ? his.Due330Amt : 0)
                               //                     + (his.Due360Amt > 0 ? his.Due360Amt : 0)
                               //                     + (his.DueOver360Amt > 0 ? his.DueOver360Amt : 0),
                               OneYearSales = dftcfg == null ? 0 : dftcfg.OneYearSales
                           }).ToList<AgingReport>();

                if (custAgingHis.Count == 0)
                {
                    throw new OTCServiceException("There is no aging report uploaded in current period!");
                }
                NpoiHelper helper = new NpoiHelper(strTmpPath);
                helper.Save(strReportPath, true);
                helper = new NpoiHelper(strReportPath);
                string sheetName = "";
                sheetName = "RAW";

                helper.ActiveSheetName = sheetName;

                int iRow;
                iRow = 2;
                string strInital = "";

                if (custAgingHis.Count > 0)
                {
                    foreach (AgingReport cust in custAgingHis)
                    {
                        strInital = cust.CustomerName.Substring(0, 1).ToUpper();
                        Regex reg = new Regex(@"[A-Za-z]");
                        if (!reg.IsMatch(strInital))
                        {
                            strInital = "A";
                        }
                        cust.DueOver180Amt = cust.Due360Amt + cust.DueOver360Amt;
                        cust.DueOver90Amt = cust.DueOver180Amt + cust.Due180Amt;
                        cust.DueOver60Amt = cust.DueOver90Amt + cust.Due90Amt;
                        cust.AdjustedDueOver90Amt = (cust.Due180Amt > 0 ? cust.Due180Amt : 0) +
                                                    (cust.Due360Amt > 0 ? cust.Due360Amt : 0) +
                                                    (cust.DueOver360Amt > 0 ? cust.DueOver360Amt : 0);
                        cust.AdjustedDueOver60Amt = cust.AdjustedDueOver90Amt + (cust.Due90Amt > 0 ? cust.Due90Amt : 0);
                        if (cust.OneYearSales == null)
                        {
                            cust.OneYearSales = 0;
                        }
                        //helper.AddRow(iRow, true, iRow - 1);
                        helper.SetData(iRow, 0, cust.LegalEntity);
                        helper.SetData(iRow, 1, cust.Team);
                        reg = new Regex(@"^\d+$");
                        if (reg.IsMatch(cust.CustomerNum))
                        {
                            helper.SetData(iRow, 2, Convert.ToInt64(cust.CustomerNum));
                        }
                        else
                        {
                            helper.SetData(iRow, 2, cust.CustomerNum);
                        }
                        helper.SetData(iRow, 3, cust.CustomerName);
                        helper.SetData(iRow, 4, strInital);
                        helper.SetData(iRow, 5, cust.BillGroupCode);
                        helper.SetData(iRow, 6, cust.BillGroupName);
                        helper.SetData(iRow, 7, cust.Country);
                        helper.SetData(iRow, 8, cust.CreditTrem);
                        helper.SetData(iRow, 9, cust.CreditLimit);
                        helper.SetData(iRow, 10, cust.Collector);
                        helper.SetData(iRow, 11, cust.Collectorsys);
                        helper.SetData(iRow, 12, cust.Sales);
                        helper.SetData(iRow, 13, cust.TotalAmt);
                        helper.SetData(iRow, 14, cust.CurrentAmt);
                        helper.SetData(iRow, 15, cust.Due30Amt);
                        helper.SetData(iRow, 16, cust.Due60Amt);
                        helper.SetData(iRow, 17, cust.Due90Amt);
                        helper.SetData(iRow, 18, cust.Due180Amt);
                        helper.SetData(iRow, 19, cust.Due360Amt);
                        helper.SetData(iRow, 20, cust.DueOver360Amt);
                        helper.SetData(iRow, 21, cust.DueOver60Amt);
                        helper.SetData(iRow, 22, cust.AdjustedDueOver60Amt);
                        helper.SetData(iRow, 23, cust.DueOver90Amt);
                        helper.SetData(iRow, 24, cust.AdjustedDueOver90Amt);
                        helper.SetData(iRow, 25, cust.DueOver180Amt);
                        helper.SetData(iRow, 26, cust.OneYearSales);
                        //helper.SetData(iRow, 27, DateTime.Now.ToString("yyyyMMddhhmmss"));
                        helper.CopyStyle(iRow, iRow - 1);
                        iRow++;
                    }
                }
                helper.DeleteRows(1);
                helper.Save(strReportPath, true);

                HttpRequest request = HttpContext.Current.Request;
                StringBuilder appUriBuilder = new StringBuilder(request.Url.Scheme);
                appUriBuilder.Append(Uri.SchemeDelimiter);
                appUriBuilder.Append(request.Url.Authority);
                if (String.Compare(request.ApplicationPath, @"/") != 0)
                {
                    appUriBuilder.Append(request.ApplicationPath);
                }
                strportPath = appUriBuilder.ToString() + strportPath.Trim('~') + strReportName;

                fileService.downloadFileInsert(strReportName, strportPath, strimportId, UploadStates.Success);
            #endregion
            }
            catch (OTCServiceException)
            {
                throw;
            }
            catch (Exception ex)
            {
                fileService.downloadFileInsert(strReportName, "", strimportId, UploadStates.Failed);
                throw new Exception(ex.Message);
            }
            
        }

        /// <summary>
        /// Upload One Year Sales
        /// </summary>
        /// <returns></returns>
        public void uploadOneYearSales()
        {
            List<CustomerGroupCfg> listTable = new List<CustomerGroupCfg>();
            Customer cusfrFile = new Customer();
            Customer updateCust = new Customer();
            string strimportId;
            strimportId = System.Guid.NewGuid().ToString("N");
            string strCode = "";
            string strpath = "";
            FileUploadHistory fileUpHis = new FileUploadHistory(); 
            FileService fileService = SpringFactory.GetObjectImpl<FileService>("FileService");

            //add by gai start 

            // Get the threshold of each level from data base. Will always return LR;
            //var thresholdConfig = BDService.GetSysConfigByCode("001");

            // decide class from total AR aging amount of this customer in aging staging records.
            //decimal dec = decimal.Parse(thresholdConfig.CfgValue);
            //add by gai end

            List<CustomerGroupCfgStaging> grpStaings = new List<CustomerGroupCfgStaging>();
            //List<GroupStaing> grpStaingEx = new List<GroupStaing>();
            //grpStaingEx = CommonRep.GetDbSet<GroupStaing>().ToList();
            CustomerGroupCfgStaging grpStaing;

            bool blStartData = false;

            //string strInit = "";

            //string strMes = "";
            //int errorNo = 0;
            try
            {
                
                //get T_customer
                listTable = (from cus in GetAllCustomerGroup()
                             where cus.Deal == CurrentDeal
                             select cus).ToList<CustomerGroupCfg>();

                //get the fileName from table
                strCode = Helper.EnumToCode<FileType>(FileType.OneYearSales);
                fileUpHis = fileService.GetNewestData(strCode);

                if (fileUpHis == null)
                {
                    throw new Exception("import file is not found!");
                }

                //read excel file
                strpath = fileUpHis.ArchiveFileName;
                
                //
                NpoiHelper helper = new NpoiHelper(strpath);
                string sheetName = "";
                sheetName = "Summary";

                helper.ActiveSheetName = sheetName;

                int i = 0;
                string strImpName;
                string strImpCode;
                string strImpAmt;
                do
                {
                    strImpCode = helper.GetValue(i, 0) == null ? null : helper.GetValue(i, 0).ToString();
                    strImpName = helper.GetValue(i, 1) == null ? null : helper.GetValue(i, 1).ToString();
                    strImpAmt = helper.GetValue(i, 2) == null ? null : helper.GetValue(i, 2).ToString();

                    i = i + 1;

                    //"Factory Group Name"
                    if (!blStartData &&
                        strImpCode.ToUpper() == "FACTORY GROUP CODE"
                        && strImpName.ToUpper() == "FACTORY GROUP NAME"
                        && strImpAmt.ToUpper() == "TOTAL")
                    {
                        blStartData = true;
                        continue;
                    }
                    else if (blStartData && !string.IsNullOrEmpty(strImpName))
                    {
                        /* group check delete
                        if (listTable.Where(o => o.BillGroupCode == strImpCode) == null)
                        {
                            strInit = strImpCode.Substring(1,1).ToUpper();
                            if (strImpName == strImpCode || strInit != "U" || strInit != "V")
                            {
                                errorNo++;
                                if (errorNo <= 10)
                                {
                                    strMes += "FACTORY GROUP CODE:[" + strImpCode
                                                + "];FACTORY GROUP NAME:[" + strImpName
                                                + "];TOTAL:[" + strImpAmt + "]" + strEnter;
                                } 
                                continue;
                            }
                        }*/
                        grpStaing = new CustomerGroupCfgStaging();
                        grpStaing.Deal = CurrentDeal;
                        grpStaing.BillGroupCode = strImpCode;
                        grpStaing.BillGroupName = strImpName;
                        grpStaing.OneYearSales = dataConvertToDec(strImpAmt);
                        grpStaing.UploadTime = AppContext.Current.User.Now;
                        grpStaing.ImportId = strimportId;
                        // TODO: zhichao comment this code, we should move value class calculation to submit not upload.
                        //if (grpStaing.OneYearSales > dec)
                        //{
                        //    grpStaing.VClass = CustomerClass.HV.ToString();
                        //}
                        //else
                        //{
                        //    grpStaing.VClass = CustomerClass.LV.ToString();
                        //}
                        grpStaings.Add(grpStaing);
                    }

                } while (!(string.IsNullOrEmpty(strImpName) && blStartData));

                using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 30, 0)))
                {
                    string DelSql;

                    DelSql = "delete from T_CUSTOMER_GROUP_CFG_STAGING WHERE DEAL = '"
                               + CurrentDeal + "';";
                    CommonRep.GetDBContext().Database.ExecuteSqlCommand(DelSql);

                    (CommonRep.GetDBContext() as OTCEntities).BulkInsert(grpStaings);

                    scope.Complete();
                }
                //CommonRep.RemoveRange(grpStaingEx);
                //CommonRep.AddRange(grpStaings);
                

                //CommonRep.Commit();

                strCode = Helper.EnumToCode<FileType>(FileType.OneYearSales);
                fileUpHis = fileService.GetNewestData(strCode);
                fileUpHis.ProcessFlag = Helper.EnumToCode<UploadStates>(UploadStates.Success);
                fileUpHis.DataSize = grpStaings.Count;
                fileUpHis.ImportId = strimportId;
                CommonRep.Commit();

                // group check delete
                //if (!string.IsNullOrEmpty(strMes))
                //{
                //    strMes = "There are " + errorNo.ToString() + " groups cann't import!" + strEnter + strMes;
                //    throw new OTCServiceException(strMes);
                //}
            }
            catch (Exception ex)
            {
                fileUpHis.ProcessFlag = Helper.EnumToCode<UploadStates>(UploadStates.Failed);
                CommonRep.Commit();
                throw ex;
            }
            finally
            {
                
            }
            
         }

        public List<CustomerAging> GetAgingByNum(string custNum)
        {
            return CommonRep.GetQueryable<CustomerAging>().ToList().FindAll(m => m.CustomerNum == custNum && m.Deal == CurrentDeal);
        }

        public Boolean checkDelCustomer(Customer cust)
        {
            string custNum=cust.CustomerNum;
            int contCount = CommonRep.GetQueryable<Contactor>().Where(o => o.CustomerNum == custNum && o.Deal == AppContext.Current.User.Deal).Count();
            int bankCount = CommonRep.GetQueryable<CustomerPaymentBank>().Where(o => o.CustomerNum == custNum && o.Deal == AppContext.Current.User.Deal).Count();
            int circleCount = CommonRep.GetQueryable<CustomerPaymentCircle>().Where(o => o.CustomerNum == custNum && o.Deal == AppContext.Current.User.Deal).Count();
            int agingCount=GetAgingByNum(custNum).Count();
            int invoiceCount = CommonRep.GetQueryable<InvoiceAging>().Where(o => o.CustomerNum == custNum && o.Deal == AppContext.Current.User.Deal).Count();
            if (contCount == 0 && bankCount == 0 && circleCount == 0 && agingCount == 0 && invoiceCount==0)
            {
                return true;
            }
            return false;
        }
    }

    /// <summary>
    /// Include the method needed to assign the collector to customer aging automatically.
    /// </summary>
    public interface ICollectorAssignmentStratege
    {
        List<Collector> Collectors { get; set; }
        String AssignCollector(CustomerClass cclass);
    }

    public class ByValueAssignment : ICollectorAssignmentStratege
    {
        public List<Collector> Collectors { get; set; }
        public ICustomerService CustService { get; set; }

        /// <summary>
        /// Assigned tasks to collector
        /// </summary>
        /// <param name="cust"></param>
        public string AssignCollector(CustomerClass cclass)
        {
            // filter collector list by value factor.
            // filter collectors by ValueClass = cusClass
            var filtedCollectors = from sysUser in Collectors
                                   where sysUser.ValueClass == Helper.EnumToCode<CustomerClass>(cclass)
                                   select sysUser;

            // The collectors does not exist
            Boolean flag = false;

            // Get collector assignments for all filtered collectors, balance collector assignments
            foreach (var coll in filtedCollectors)
            {
                // The collectors exist
                flag = true;

                // 1, Get existing customer aging count(from Customer Aging) by collector and set "AssigmentCount" property if it is -1.
                if (coll.AssigmentCount == -1)
                {
                    var retCusAging = CustService.GetCustomerAgingByCollector(coll.EID);
                    if (retCusAging == null)
                    {
                        coll.AssigmentCount = 0;
                    }
                    else
                    {
                        coll.AssigmentCount = retCusAging.Count();
                    }
                }
            }

            // The collectors does not exist
            if (flag == false)
            {
                return string.Empty;
            }

            // 2, choose the minumum in all collectors.
            int min = filtedCollectors.Min(o => o.AssigmentCount);
            var minCol = filtedCollectors.Where(o => o.AssigmentCount == min).Select(o => o).FirstOrDefault();
            // 3, increase the "AssigmentCount" property of choosed collector.
            minCol.AssigmentCount++;

            return minCol.EID;
        }

    }
}
